SQL Reference ยท Statements and Clauses
SQL GROUP BY clause
Reference for the SQL GROUP BY clause: group rows, use COUNT, SUM and AVG per group, group by several columns, NULL groups and dialect rules.
GROUP BY collapses rows that share the same values in the listed expressions into one row per group. You then use aggregate functions such as COUNT, SUM and AVG to summarize each group.
Without GROUP BY, an aggregate in the select list turns the entire table into a single group. With it, you get one result row per distinct combination of grouping values.
SELECT category_id, COUNT(*) AS products FROM products GROUP BY category_id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT grouping_expr [, ...], aggregate(...) [, ...] FROM table [ WHERE condition ] GROUP BY expression [, expression ...] [ HAVING group_condition ];
| Part | Description |
|---|---|
GROUP BY expression |
A column or expression. Rows with equal values (NULLs count as equal) fall in the same group. |
| Select list | May contain only grouping expressions, aggregates, constants, and functions of those. |
WHERE |
Filters rows before grouping. |
HAVING |
Filters groups after aggregation. See HAVING. |
Returns: one row per group.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Non-grouped, non-aggregated column in select list | Allowed (arbitrary row) | Error unless functionally dependent on the primary key | Error with ONLY_FULL_GROUP_BY (default) |
Error | Error |
| Group by select alias or position | Both | Both | Both | Neither (repeat the expression) | Neither |
GROUPING SETS, ROLLUP, CUBE |
No | Yes | WITH ROLLUP |
Yes | Yes |
-- PostgreSQL / SQL Server / Oracle SELECT category_id, COUNT(*) FROM products GROUP BY ROLLUP (category_id);
See GROUPING SETS, ROLLUP and CUBE.
Usage notes
- Every select item must be grouped or aggregated. In SQLite and old MySQL, selecting an ungrouped column silently returns a value from an arbitrary row in the group, which is almost never what you mean.
- NULL is one group. All NULL values in a grouping column land in a single group, as the
department_idresult shows. - Group by the expression, not just the column. To group by year, group by the same expression you select (
strftime('%Y', hire_date)), or by an alias where your dialect allows it. - Filter early. Put conditions that do not depend on aggregates in
WHERE: fewer rows to group means faster queries. - Result order is not guaranteed. Add
ORDER BYif the order matters; some engines sort as a side effect, but you cannot rely on it. - Aggregates ignore NULL (except
COUNT(*)), so averages and counts per group may differ from the row count.