Syntax

The syntax of the optional GROUP BY clause is as follows:

GROUP BY { expression | ROLLUP ( expr_list ) |
  CUBE ( expr_list ) | GROUPING SETS ( expr_list ) } [, ...]

Description

The GROUP BY clause condenses all selected rows that share the same values for the grouped expressions into a single row. expression can be an input column name, or the name or ordinal number of an output column that is specified in the SELECT list. It can also be an expression formed from the values of input columns. In case of ambiguity, 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 GROUP BY clause. These extensions are used to support multidimensional analysis.

If aggregate functions are used, the aggregate functions are computed across all rows in each group. This produces a separate value for each group. If no GROUP BY clause is specified, an aggregate function produces a single value computed across all the selected rows. If the GROUP BY clause is used, the SELECT list expressions cannot refer to ungrouped columns except within 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)