Organizing query results into groups: the group by clause

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 be no greater than 64K.

NoteYou cannot group by 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 the 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.