Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT category_id, COUNT(*) AS products
FROM products
GROUP BY category_id;

Try it

SQL One row per group ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Several grouping columns and NULL groups ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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_id result 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 BY if 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.