The GROUP BY clause has the following syntax:
GROUP BY [ALL] aggregate_free_expression
[, aggregate_free_expression]...
Remember that the order of the clauses in the SELECT statement is significant. You can omit any of the optional clauses, but when you use them, they must appear in the appropriate order. For the complete syntax of the SELECT statement, see Creating Basic Queries.
You can group by an expression as long as it does not include aggregate functions. For example:
SELECT AVG(ytd_sales), ytd_sales * royalty |
||
FROM titles |
||
GROUP BY ytd_sales * royalty |
||
---------- |
---------- |
|
(null) |
(null) |
|
111 |
1110 |
|
375 |
3750 |
|
2032 |
24384 |
|
2045 |
24540 |
|
3336 |
33360 |
|
3876 |
38760 |
|
4072 |
40720 |
|
4095 |
40950 |
|
8780 |
140480 |
|
15096 |
211344 |
|
18722 |
449328 |
|
22246 |
533904 |
|
(13 row(s) affected) |
||
However, you cannot group by an alias.
You can correct this query if you group by type, title_id.
You must also include in the GROUP BY list all non-aggregates in the select list. In the example above, if the GROUP BY contained only 'GROUP BY TYPE', an error would result, stating that title_id is contained in the select list but not in the grouping. Hence, title_id would either need to be removed from the select list or added to the group by list.
This statement produces an error message:
SELECT Category = type, title_id, avg(price), avg(advance) FROM titles GROUP BY Category, title_id
You can list more than one column in the GROUP BY clause to nest groups ¾ that is, you can group a table by any combination of columns. For example, this statement finds the average price and the sum of year-to-date sales, grouped by type and publisher ID:
SELECT type, pub_id, 'avg' = AVG(price), 'sum' = sum(ytd_sales) FROM titles GROUP BY type, pub_id type pub_id avg sum ------------ ------ -------------------------- ----------- business 0736 11.96 18722 business 1389 17.31 12066 mod_cook 0877 11.49 24278 popular_comp 1389 21.48 12875 psychology 0736 45.93 9564 psychology 0877 21.59 375 trad_cook 0877 15.96 19566 UNDECIDED 0877 (null) (null) (8 row(s) affected)