The GROUP BY clause is used in SELECT statements to divide a table into groups. You can group by column name or by the results of computed columns when using numeric datatypes. You cannot group by columns of bit, text, or image datatypes. A GROUP BY clause almost always appears in statements that also include aggregate functions, in which case the aggregate function produces a value for each group. These values are called vector aggregates. (As mentioned earlier, a scalar aggregate is a single value produced by an aggregate function without a GROUP BY clause.)
This statement finds the average advance and sum of year-to-date sales for each type of book:
SELECT type, AVG(advance), SUM(ytd_sales) |
|||
FROM titles |
|||
GROUP BY type |
|||
type ------------ -------------------------- ----------- business 6,281.25 30788 mod_cook 7,500.00 24278 popular_comp 7,500.00 12875 psychology 4,255.00 9939 trad_cook 6,333.33 19566 UNDECIDED (null) (null) (6 row(s) affected)
The summary values produced by SELECT statements with GROUP BY and aggregate functions appear as new columns in the results.
Important The output for statements involving the GROUP BY clause depends on the sort order chosen during installation. The GROUP BY clause does not order the results. For information about the effects of different sort orders, see Microsoft SQL Server Setup.