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 group by columns (or expressions) is not explicitly limited. The only limit of group by results is that the width of the group by columns plus the aggregate results cannot be larger than 64K.
You cannot use group by with columns
of text, unitext, 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 results by title type:
select type, advance from titles group by type
type advance ------------ --------- popular comp 7,000.00 popular comp 8,000.00 popular comp NULL business 5,000.00 business 5,000.00 business 10,125.00 mod_cook 0.00 mod_cook 15,000.00 trad_cook 7,000.00 trad_cook 4,000.00 trad_cook 8,000.00 UNDECIDED NULL psychology 7,000.00 psychology 2,275.00 psychology 6,000.00 psychology 2,000.00 psychology 4,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 ------------ ------------------------ popular_comp 15,000.00 business 25,125.00 mod_cook 15,000.00 trad_cook 19,000.00 UNDECIDED NULL psychology 21,275.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”).
See the Reference Manual:Commands.
