GROUP BY ​

GROUP BY combines rows with equal grouping values and calculates aggregates independently for each group. The result usually contains one row per distinct grouping key.

All NULL values in a grouping key belong to the same group. Text grouping uses the selected collation.

Syntax ​

SELECT key_expression, aggregate_function(value)
FROM source
[WHERE condition]
GROUP BY key_expression [, ...]
[HAVING aggregate_condition]

Grouping can use expressions or result-column positions. Explicit expressions are easier to maintain when the projection changes. GROUP BY does not sort the final result; use ORDER BY.

ROLLUP, CUBE and GROUPING SETS are not supported.

Examples ​

Totals per region ​

CREATE TABLE sales (region TEXT, amount INTEGER);
INSERT INTO sales VALUES ('East', 10), ('East', 20), ('West', 5);

SELECT region, count(*) AS orders, sum(amount) AS total
FROM sales
GROUP BY region
ORDER BY region
FORMAT csv;

"East",2,30
"West",1,5

Grouping an expression ​

CREATE TABLE visits (country TEXT);
INSERT INTO visits VALUES ('GB'), ('gb'), ('FR'), (NULL), (NULL);

SELECT lower(country) AS country, count(*) AS visits
FROM visits
GROUP BY lower(country)
ORDER BY country NULLS LAST
FORMAT csv;

"fr",1
"gb",2
\N,2

Without GROUP BY, an aggregate query treats the whole input as one group, including empty input. A grouped query with no admitted rows returns no groups.

See aggregate functions and HAVING.