GROUP BY

An aggregate on its own gives one number for the whole table. GROUP BY gives one number per category.

SQL
SELECT dept_id,
       COUNT(*)              AS staff,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

The database sorts the rows into buckets by dept_id, then runs the aggregates once per bucket. Four buckets in, four rows out.

The rule for what may appear in SELECT

Every column in the SELECT list must be either:

named in the GROUP BY, or
wrapped in an aggregate.

Nothing else. SELECT dept_id, first_name, COUNT(*) … GROUP BY dept_id is invalid, because department 1 has four different first names and only one row is coming out. There is no answer to give.

PostgreSQL, SQL Server and Oracle reject it. MySQL and SQLite may not — they pick a value from an arbitrary row in the group and return it without comment. That is the failure mode to fear: not an error, just a plausible-looking number that changes when the data changes. If you work on MySQL, turn on ONLY_FULL_GROUP_BY.

Grouping by more than one column

SQL
SELECT dept_id, active, COUNT(*) AS people
FROM employees
GROUP BY dept_id, active
ORDER BY dept_id, active;

Now the bucket is the combination. Department 2 splits into two rows because it contains both an active and an inactive employee. Each extra GROUP BY column can only make the groups smaller and more numerous.

NULL is a group

SQL
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY country;

The customer with no country gets a bucket of its own. This is a deliberate inconsistency in SQL worth knowing by heart: WHERE country = NULL matches nothing, because NULLs are never equal — but GROUP BY treats all NULLs as one group, and so does DISTINCT. Grouping asks "are these the same?", not "are these equal?", and two unknowns count as the same unknown.

Aggregates still skip NULLs inside each group

SQL
SELECT dept_id,
       COUNT(*)      AS people,
       COUNT(salary) AS with_salary,
       AVG(salary)   AS avg_salary
FROM employees
WHERE dept_id = 3
GROUP BY dept_id;

Department 3 has two people but one recorded salary, so its "average" is just that one person's salary. Nothing warns you. Showing COUNT(*) next to COUNT(col) in any grouped report is the cheapest way to make that visible.

Filter before grouping with WHERE

SQL
SELECT dept_id, COUNT(*) AS active_staff
FROM employees
WHERE active = 1
GROUP BY dept_id
ORDER BY dept_id;

WHERE runs first, so only active employees ever reach a bucket. Filtering early is both correct here and cheaper — fewer rows to sort into groups.

Grouping by an expression

You can group by something computed, as long as you repeat the expression (the SELECT alias is not available to GROUP BY in standard SQL, for the same execution-order reason as WHERE):

SQL
SELECT substr(placed_at, 1, 7) AS month,
       COUNT(*)                AS orders,
       ROUND(SUM(amount), 2)   AS revenue
FROM orders
GROUP BY substr(placed_at, 1, 7)
ORDER BY month;

Monthly revenue — the single most common shape of business query there is. Every database has its own date functions for this (DATE_TRUNC in PostgreSQL, DATE_FORMAT in MySQL, FORMAT in SQL Server); the substring works here because the dates are ISO-formatted text.

PostgreSQL and MySQL do allow GROUP BY to reference a SELECT alias as an extension. It is convenient and it is not portable.

Common mistakes

A bare column in SELECT — an error on strict databases, silently arbitrary on lax ones.
Forgetting ORDER BY — GROUP BY does not promise sorted output, whatever your database happens to do today.
Expecting NULLs to be excluded — they form their own group.
Grouping by a column with a different granularity than you meant — grouping by a full timestamp gives one group per row.

Interview question

Why can't you SELECT a column that is not in the GROUP BY?

Because the group collapses to one row and that column has many values in it, so there is no single correct answer. A strong answer adds that MySQL and SQLite may return an arbitrary one instead of erroring, which makes the bug harder to find.

Check yourself

Count orders per customer.
Why does GROUP BY country produce a row for NULL when WHERE country = NULL produces none?
What happens to the group count when you add a second GROUP BY column?