Expressions and group by

Another Transact-SQL extension allows you to group by an expression that does not include aggregate functions. For example:

select avg(total_sales), total_sales * price 
from titles 
group by total_sales * price

---------      -------------
     NULL               NULL
      111             777.00  
      375           7,856.25  
      375           8,096.25 
     2045          22,392.75  
     3336          26,654.64  
     2032          40,619.68  
     3876          46,318.20  
    18722          55,978.78  
     4095          61,384.05  
    22246          66,515.54  
     4072          81,399.28  
     4095          81,859.05  
     4095          81,900.00  
    15096         180,397.20  
     8780         201,501.00 
 
(16 rows affected)

The expression “total_sales * price” is allowed.

You cannot group by a column heading, also known as an alias, although you can still use one in your select list. This statement produces an error message:

select Category = type, title_id, avg(price), avg(advance) 
from titles 
group by Category

The group by clause should be “group by type”, not “group by Category”.

select Category = type, title_id, avg(price), avg(advance) 
from titles 
group by type