Expressions and group by

Use group by for an expression that does not include aggregate functions.

For example:
select avg(total_sales), total_sales * price 
from titles 
group by total_sales * price 
---------      -------------
     2045          22,392.75
     2032          40,619.68
     4072          81,399.28
     NULL               NULL
     4095          61,384.05
    18722          55,978.78
      375           7,856.25
    15096         180,397.20
     3876          46,318.20
      111             777.00
     3336          26,654.64
     4095          81,859.05
    22246          66,515.54
     8780         201,501.00
      375           8,096.25
     4095          81,900.00 
 
(16 rows affected) 

The expression “total_sales * price” is allowed.

You cannot use group by on 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
------------------
Msg 207, Level 16, State 4:
Line 1: 
Invalid column name 'Category'
Msg 207, Level 16, State 4:
Line 1:
Invalid column name '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
--------------
         21.48
         13.73
         11.49
         15.96
           NULL
13.50

(6 rows affected)