Menu
I have a column that looks something like this:
CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product And I would like to put it in my GROUP BY clause, but this seems to cause problems because there is an aggregate function in column. Is there a way to GROUP BY a column alias such as some_product in this case, or do I need to put this in a subquery and group on that?
420
The CASE statement can also be used in conjunction with the GROUP BY statement in order to apply aggregate functions.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Yes. i put them on my answer: 1,2,3,4. All except the COUNT.
If you don't specify GROUP BY , aggregate functions operate over all the records selected. In that case, it doesn't make sense to also select a specific column like EmployeeID .
My guess is that you don't really want to GROUP BY some_product.
The answer to: "Is there a way to GROUP BY a column alias such as some_product in this case, or do I need to put this in a subquery and group on that?" is: You can not GROUP BY a column alias.
The SELECT clause, where column aliases are assigned, is not processed until after the GROUP BY clause. An inline view or common table expression (CTE) could be used to make the results available for grouping.
Inline view:
select ... from (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product from ... group by col1, col2 ... ) T group by some_product ... CTE:
with T as (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product from ... group by col1, col2 ... ) select ... from T group by some_product ... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
