SQL GROUP BY: how grouping and aggregate functions actually work

What GROUP BY actually does

A plain SELECT gives you back rows. One row in the table becomes one row in the result. GROUP BY changes that contract: it collapses many rows into one row per group, and you choose what to group on.

Think of a table of employees where each row is one person. If you group by department, SQLite sorts the rows into piles — one pile per distinct department value — and then hands you a single row for each pile. The question it answers is no longer "who works here?" but "what is true of each department?"

That is the whole idea. Everything else about GROUP BY follows from it: because you get one row per pile, every column you select has to make sense for a whole pile rather than for one person.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Why grouping is needed at all

You can already filter with WHERE and sort with ORDER BY, so it is fair to ask what grouping adds. The answer is that WHERE and ORDER BY both operate on individual rows. Neither can compare a row to its neighbours, and neither can produce a number that describes a set.

Almost every real question about data is a question about a set: how many orders did each customer place, what did each category earn, which class has the highest average. None of those can be written with WHERE alone, because the answer does not exist in any single row — it only exists once rows are combined.

GROUP BY is the mechanism that creates those sets, and aggregate functions are what read a value out of each one.

GROUP BY and aggregate functions are two halves of one idea

An aggregate function takes many values and returns one. SQLite's core five are COUNT, SUM, AVG, MIN and MAX. On their own, with no GROUP BY, they treat the entire table as a single group and give you exactly one row back.

Add GROUP BY and the same functions run once per group instead of once per table. That is the only thing that changes. If you understand what COUNT(*) does to a whole table, you already understand what it does to a group.

Two details about counting are worth committing to memory, because they are a common source of quietly wrong answers. COUNT(*) counts rows — every row in the group, including rows where columns are NULL. COUNT(column) counts non-NULL values in that column, so it can legitimately return a smaller number than COUNT(*) for the very same group.

SUM and AVG also skip NULLs rather than treating them as zero. A column with four numbers and one NULL has an AVG computed over four values, not five. That is usually what you want, but only if you know it is happening.

• COUNT(*) — how many rows are in this group, NULLs included
• COUNT(column) — how many non-NULL values this column has in this group
• SUM(column) — total of the non-NULL values
• AVG(column) — mean of the non-NULL values; always returns a floating-point number in SQLite, even when every input is an integer
• MIN(column) / MAX(column) — smallest and largest non-NULL value

The mistakes almost everyone makes

The first and biggest one: selecting a column that is not in the GROUP BY and is not inside an aggregate function. Written out, it looks harmless — SELECT name, department, COUNT(*) FROM employees GROUP BY department. But the HR group contains several different names, and the query asks for one. Which name should it return?

PostgreSQL and MySQL in its default modern configuration refuse this query outright with an error. SQLite does not. It accepts it and returns an arbitrary row's value for the bare column. This is a documented SQLite behaviour, not a bug — and it is the single most dangerous thing about learning GROUP BY on SQLite, because your query appears to work while quietly reporting a value you did not choose.

There is one exception, and it is genuinely useful: when the query uses MIN() or MAX(), SQLite guarantees that bare columns come from a row that actually produced that minimum or maximum. So selecting a name alongside MAX(salary) does give you the name of the highest-paid person in the group. Outside of MIN and MAX, treat any bare column as unreliable.

The second common mistake is trying to filter on an aggregate inside WHERE — writing WHERE COUNT(*) > 1 and getting an error. WHERE runs before grouping happens, so at that point no counts exist yet. The clause you want is HAVING.

The third is assuming the results come back in a sensible order. They often look sorted, because of how SQLite computes groups internally, but nothing guarantees it. If order matters, say so with ORDER BY.

A smaller one worth knowing: rows where the grouping column is NULL do not disappear. SQLite treats all NULLs as belonging to a single group, so you get one NULL row in the output alongside the real values.

-- Reliable: name comes from the row that produced MAX(salary)
SELECT name, department, MAX(salary)
FROM employees
GROUP BY department;

-- Unreliable on SQLite, an error on PostgreSQL/MySQL:
-- `name` is an arbitrary row's value
SELECT name, department, COUNT(*)
FROM employees
GROUP BY department;

GROUP BY vs ORDER BY

These two get confused constantly, usually because both involve putting rows together by a shared value. They do completely different things.

ORDER BY rearranges rows. Ten rows in, ten rows out, in a different sequence. Nothing is combined and nothing is lost.

GROUP BY collapses rows. Ten rows in, one row per distinct group out. Information about individual rows is deliberately thrown away, and what you get instead is a summary.

They are not alternatives, and they work well together: group first to get one row per department, then order those grouped rows by the count to see the largest department first.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

Where HAVING fits

HAVING is the filter that runs after grouping. WHERE is the filter that runs before it. That single sentence explains every difference between them.

Because WHERE runs first, it decides which rows are allowed into the piles. Because HAVING runs afterwards, it decides which finished groups survive — and it is the only place an aggregate can be tested, since aggregates do not exist until grouping has happened.

The practical order SQLite applies is: FROM to get the rows, WHERE to discard rows, GROUP BY to build the groups, HAVING to discard groups, SELECT to produce the output columns, then ORDER BY to sequence them.

A query can use both, and often should. Filtering rows early with WHERE is usually cheaper than grouping them and throwing the groups away afterwards.

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 5          -- filters rows, before grouping
GROUP BY department
HAVING COUNT(*) > 1;      -- filters groups, after grouping