SQL GROUP BY CASE statement with aggregate function

Question

I have a column that looks something like this   CASE     WHEN col1  gt  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

User · Answer

I think the answer is pretty simple  unless I m missing something    SELECT     CASE     WHEN col1  gt  col2 THEN SUM col3 col4      ELSE 0 END AS some product FROM some table GROUP BY CASE     WHEN col1  gt  col2 THEN SUM col3 col4      ELSE 0 END   You can put the CASE STATEMENT in the GROUP BY verbatim  minus the alias column name

User · Answer

If you are grouping by some other value  then instead of what you have   write it as   Sum CASE WHEN col1  gt  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  gt  col2 Then col3 col4 Else 0 End  as SumSomeProduct From      Group By Case When col1  gt  col2 Then col3 col4 Else 0 End    Finally  if you want to group By the actual aggregate  Select SumSomeProduct  Count      lt other aggregate functions gt  From  Select  lt other columns you are grouping By gt          Sum Case When col1  gt  col2            Then col3 col4 Else 0 End  as SumSomeProduct       From Table       Group By  lt Other Columns gt    As Z Group by SumSomeProduct

User · Answer

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  gt  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

User · Answer

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  gt  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  gt  col2 THEN SUM col3 col4  ELSE 0 END AS some product    from        group by col1  col2       select     from T group by some product

[sql] SQL GROUP BY CASE statement with aggregate function

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to group-by