SELECT Statement: The GROUP BY Clause in SQL
- 10.1 Introduction
- 10.2 Grouping on One Column
- 10.3 Grouping on Two or More Columns
- 10.4 Grouping on Expressions
- 10.5 Grouping of NULL Values
- 10.6 General Rules for the GROUP BY Clause
- 10.7 Complex Examples with GROUP BY
- 10.8 Grouping with WITH ROLLUP
- 10.9 Grouping with WITH CUBE
- 10.10 Grouping Sets
- 10.11 Grouping with ROLLUP and CUBE
- 10.12 Combining Grouping Sets
- 10.13 Answers
10.1 Introduction
The GROUP BY clause groups rows on the basis of similarities between them. You could, for example, group all the rows in the PLAYERS table on the basis of the place of residence; the result would be one group of players per town. From there you could query how many players there are in each group. The question that is actually answered is then: How many players live in each town? Other examples follow: How many matches have been played per team, and how much has been incurred in penalties per player? In short, the GROUP BY clause is frequently used to formulate questions based on the word per.
By adding aggregation functions, such as COUNT and SUM, to a select block with the use of a GROUP BY clause, data can be aggregated. These functions owe their name to this. Aggregation means that you ask not for the individual values, but for summations, averages, frequencies, and subtotals.
<group by clause> ::= GROUP BY <group by specification list> [ WITH { ROLLUP | CUBE } ] <group by specification list> ::= <group by specification> [ { , <group by specification> }... ] <group by specification> ::= <group by expression> | <grouping sets specification> | <rollup specification> <grouping sets specification> ::= GROUPING SETS ( <grouping sets specification list> ) <grouping sets specification list> ::= <grouping sets specification> [ { , <grouping sets specification> }... ] <grouping sets specification> ::= <group by expression> | <rollup specification> | ( <grouping sets specification list> ) <rollup specification> ::= ROLLUP ( <group by expression list> ) | CUBE ( <group by expression list> ) | ( ) <group by expression> ::= <scalar expression>