GROUP BY
SQL · Grouping Rows
Theory
GROUP BY collapses rows that share the same value(s) into one row per group, so aggregate functions can summarise each group: COUNT(*) counts rows, SUM and AVG add and average, MIN and MAX find extremes. GROUP BY dept with COUNT(*) and SUM(salary) turns five employee rows into one summary row per department.
Every column in SELECT must either be in GROUP BY or be wrapped in an aggregate; otherwise the engine cannot know which value to show (MySQL in some modes and SQLite allow it but return an arbitrary value, which is a common bug). Grouping on several columns makes one group per unique combination. NULLs in a grouping column form their own single group. GROUP BY runs after WHERE, so WHERE filters rows first, and HAVING then filters groups.
Why we use it
Raw rows answer "what happened"; GROUP BY answers "how much per category": total sales per city, headcount per department, average order value per month. It is the core of almost every report.
When to use it
Use GROUP BY whenever you need one result row per category with totals, counts or averages. If you need the aggregate next to each detail row instead of collapsing rows, use window functions such as SUM() OVER (PARTITION BY ...).
How it works
- Rows arrive after WHERE
- Read the grouping column value(s) of each row
- Does a group with this value exist? Yes: Add the row to that group. No: Start a new group.
- Compute aggregates (COUNT, SUM, AVG...) per group
- Return one row per group
Syntax
SELECT column, AGG(column)
FROM table
GROUP BY column;Example
SELECT department, COUNT(*) FROM employees GROUP BY department;Key points
- Every non-aggregated SELECT column must appear in GROUP BY
- Runs after WHERE, before HAVING and ORDER BY
- Grouping on several columns gives one group per combination
- NULL values form one group of their own
- Aggregates skip NULLs, except COUNT(*)