Organize 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 cannot be larger than 64K.

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 the Reference Manual:Commands.

• group by and SQL Standards
  The SQL standards for group by are more restrictive than the default SAP ASE standard.
• Nest Groups with group by
  Nest groups by including more than one column in the group by clause. Once the sets are established with group by, the aggregates are applied.
• Reference Other Columns in Queries Using group by
  SQL standards state that the group by clause must contain items from the select list. However, Transact-SQL allows you to specify any valid column name in either the group by or select list, whether they employ aggregates or not.
• Expressions and group by
  Use group by for an expression that does not include aggregate functions.
• group by in Nested Aggregates
  Use group by to nest a vector aggregate inside a scalar aggregate.
• Null Values and group by
  If the grouping column contains a null value, that row becomes its own group in the results. If the grouping column contains more than one null value, all null values form a single group.
• where Clause and group by
  You can use a where clause in a statement with group by.
• group by and all
  The keyword all in the group by clause is a Transact-SQL enhancement. It is meaningful only if the select statement in which it is used also includes a where clause.
• Aggregates Without group by
  By definition, scalar aggregates apply to all rows in a table, producing a single value for the entire table for each function.