SQL GROUP BY CASE statement with aggregate function

Question

I have a column which 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?

Answer 1

If you are Grouping by some other value, then instead of what you have,

write it as

Sum(CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END) as SumSomeProduct

If, otoh, you want to group By the internal expression, (col3*col4) then

write the group By to match the expression w/o the SUM...

Select Sum(Case When col1 > col2 Then col3*col4 Else 0 End) as SumSomeProduct
From ...

Group By Case When col1 > col2 Then col3*col4 Else 0 End

Finally, if you want to group By the actual aggregate

Select SumSomeProduct, Count(*), <other aggregate functions>
From (Select <other columns you are grouping By>, 
      Sum(Case When col1 > col2 
          Then col3*col4 Else 0 End) as SumSomeProduct
      From Table
      Group By <Other Columns> ) As Z
Group by SumSomeProduct

Answer 2

You should be able to group by col1 and col2 only.

SELECT
    CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
FROM
    MyTable
GROUP BY
    col1, col2

Reason:

• Your "ELSE 0" is the same as "ELSE 0 * SUM(col3*col4)" effectively
• You are grouping by col1, col2 and summing col3 * col4 and you can not group by the aggregate you are grouping by.

If this is not correct, then we'll need more info, sample data, schema etc to understand what you want to do...

Answer 3

My guess is that you don't really want to GROUP BY some_product. But 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 ...