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)