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