SQL GROUP BY CASE statement with aggregate function

by

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

More Related Contents:

  1. SQL query to group by day
  2. Insert an image in a column of a table that already has 5 columns
  3. Get top 1 row of each group
  4. Referring to a Column Alias in a WHERE Clause
  5. Convert Month Number to Month Name Function in SQL
  6. How to avoid the “divide by zero” error in SQL?
  7. SET versus SELECT when assigning variables?
  8. Is there any difference between IS NULL and =NULL
  9. Generate a resultset of incrementing dates in TSQL
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. Getting result of dynamic SQL into a variable for sql-server
  12. How to get difference between two rows for a column field?
  13. Concat groups in SQL Server [duplicate]
  14. Better techniques for trimming leading zeros in SQL Server?
  15. How to print VARCHAR(MAX) using Print Statement?
  16. SQL statement to select all rows from previous day
  17. What’s the best way to select the minimum value from several columns?
  18. Splitting delimited values in a SQL column into multiple rows
  19. varbinary to string on SQL Server
  20. How to insert a blob into a database using sql server management studio
  21. separate comma separated values and store in table in sql server
  22. Best way to do nested case statement logic in SQL Server
  23. Restrict an SQL Server connection to a specific IP address
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. Can an INNER JOIN offer better performance than EXISTS
  26. SQL server identity column values start at 0 instead of 1
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. SQL Sort Order with Null values last
  29. Insert all values of a table into another table in SQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment