The group by clause divides the output of a query into groups. You can group by one or more column names, or by the results of computed columns using numeric datatypes in an expression. When used with aggregates, group by retrieves the calculations in each subgroup, and may return multiple rows. The maximum number of columns or expressions you can use in a group by clause is 31.
You cannot group by columns of text or image datatypes.
While you can use group by without aggregates, such a construction has limited functionality and may produce confusing results. The following example groups the results by title type:
select type, advance from titles group by type
type advance ------------ --------- business 5,000.00 business 5,000.00 business 10,125.00 business 5,000.00 mod_cook 0.00 mod_cook 15,000.00 UNDECIDED NULL popular_comp 7,000.00 popular_comp 8,000.00 popular_comp NULL psychology 7,000.00 psychology 2,275.00 psychology 6,000.00 psychology 2,000.00 psychology 4,000.00 trad_cook 7,000.00 trad_cook 4,000.00 trad_cook 8,000.00 (18 rows affected)
With an aggregate for the advance column, the query returns the sum for each group:
select type, sum(advance) from titles group by type
type ------------ ------------------------ UNDECIDED NULL business 25,125.00 mod_cook 15,000.00 popular_comp 15,000.00 psychology 21,275.00 trad_cook 19,000.00 (6 rows affected)
The summary values in a group by clause using aggregates are called vector aggregates, as opposed to scalar aggregates, which result when only one row is returned (see “Using aggregate functions”).
