SQL GROUP BY CASE statement with aggregate function

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 ... ) Tgroup 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 Tgroup by some_product ... 

While Shannon's answer is technically correct, it looks like overkill.

The simple solution is that you need to put your summation outside of the case statement.This should do the trick:

sum(CASE WHEN col1 > col2 THEN col3*col4 ELSE 0 END) AS some_product

Basically, your old code tells SQL to execute the sum(X*Y) for each line individually (leaving each line with its own answer that can't be grouped).

The code line I have written takes the sum product, which is what you want.

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 SumSomeProductFrom ...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 ZGroup by SumSomeProduct