This topic describes the GROUP BY clause.
Syntax
The optional GROUPBY
clause has the following form:
GROUP BY { expression | ROLLUP ( expr_list ) |
CUBE ( expr_list ) | GROUPING SETS ( expr_list ) } [, ...]
Description
The GROUP BY
clause condenses all the selected rows that share the same values for the selected
expressions to a single row. expression
can be an input column name, or the name or the ordinal number of an output column
that is specified in the SELECT
list. It can also be an expression that consists of the values of input columns.
If ambiguity occurs, a GROUP BY
name is interpreted as the name of an input column rather than an output column.
ROLLUP
, CUBE
, and GROUPING SETS
are extensions to the GROUPBY
clause for multidimensional analysis.
If aggregate functions are used, the aggregate functions are computed across all the
rows in each group. This produces a separate value for each group. If the GROUP BY
clause is unavailable, an aggregate function produces a single value that is computed
across all the selected rows. If the GROUP BY
clause is used, the SELECT
list expressions cannot refer to ungrouped columns except in aggregate functions.
This is because more than one value may be returned for an ungrouped column.
Examples
The following example computes the sum of the sal column in the emp table and groups the results by department number.
SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno;
deptno | total
--------+----------
10 | 8750.00
20 | 10875.00
30 | 9400.00
(3 rows)