GROUP BY clause

The GROUP BY clause specifies which columns should be used for grouping when performing any aggregations in a SELECT clause. If the GROUP BY clause is specified, the query is always an aggregation, even if there is no explicitly specified aggregation in the SELECT clause.

If the GROUP BY condition is specified, all tuples that have matching data in the grouping columns (i.e., all tuples belonging to the same group) will be aggregated. The values of the grouping columns themselves are not changed, and any other columns can be combined using an aggregate function (e.g. count, sum, avg, etc.).

GROUP BY ALL

Use GROUP BY ALL to group all columns in a SELECT statement that are not wrapped in aggregate functions. This simplifies the syntax by allowing you to keep the list of columns in one place, and prevents errors by keeping the SELECT granularity consistent with the GROUP BY granularity (e.g., preventing duplication).

Syntax

The following syntax variants may be used:

SELECT ...
  FROM ...
  [ ... ]
  GROUP BY groupItem [ , groupItem [ , ... ] ]
  [ ... ]
SELECT ...
  FROM ...
  [ ... ]
  GROUP BY ALL
  [ ... ]

Where:

groupItem ::= { <column_alias> | <position> | <expr> }

Parameters

  • <column_alias>
    The alias of the column specified in the SELECT clause.

  • <position>
    The position of the expression in the SELECT clause.

  • <expr>
    Any expression defined on tables in the current scope.

  • GROUP BY ALL
    Specifies that all items in a SELECT clause that do not use aggregate functions should be used for grouping.

Examples

  • Let’s calculate the number of records in the employees table belonging to each department:

    SELECT department, count(*)
    FROM employees
    GROUP BY department;

  • Calculate the average salary for each department of each division:

    SELECT division, department, avg(salary)
    FROM employees
    GROUP BY division, department;

  • Group the data by department and division to see all unique department — division pairs:

    SELECT division, department
    FROM employees
    GROUP BY ALL;