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?
This question is related to
sql
sql-server
sql-server-2005
group-by
I think the answer is pretty simple (unless I'm missing something?)
SELECT
CASE
WHEN col1 > col2 THEN SUM(col3*col4)
ELSE 0
END AS some_product
FROM some_table
GROUP BY
CASE
WHEN col1 > col2 THEN SUM(col3*col4)
ELSE 0
END
You can put the CASE STATEMENT in the GROUP BY verbatim (minus the alias column name)
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 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
Source: Stackoverflow.com