GROUP BY in SQL: The Rules, the HAVING Clause, and the Classic Mistakes

Q: Why does COUNT return 1 for records with no matches?

Because COUNT() counts rows, and a LEFT JOIN produces a row with NULL columns even when nothing matched. Count a column from the joined table instead, such as COUNT(o.id), which ignores NULLs and correctly returns zero.

GROUP BY collapses rows sharing a value into one row per distinct value, and every column in your SELECT must then be either one of the grouping columns or wrapped in an aggregate, because a group has one value for the first and many for everything else.

That single rule explains almost every error message people hit with this clause. The engine is not being awkward; it genuinely does not know which of forty employee names to show for a department row.

What follows is the rule in practice, the WHERE-versus-HAVING distinction that decides correctness and performance, and the three traps that produce quietly wrong numbers rather than errors.

The rule, and the error it produces
WHERE against HAVING
The three traps that produce wrong numbers
Grouping by expressions and by time
When a window function is the better tool
How this fits the rest of the stack
FAQ

The rule, and the error it produces

Start with something that works.

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

One row per department. department is in the GROUP BY, and the other two columns are aggregates. Every column is accounted for.

Now the version that fails:

SELECT department, name, COUNT(*) 
FROM employees
GROUP BY department;
-- ERROR: column "employees.name" must appear in the GROUP BY clause
--        or be used in an aggregate function

A department group contains many employees with many names. The engine cannot pick one, so it refuses. The fix is to decide what you actually meant: group by name as well, aggregate it, or drop it.

-- One row per department, with names collected.
SELECT department, COUNT(*) AS headcount, STRING_AGG(name, ', ') AS members
FROM employees
GROUP BY department;

-- Or one row per department and name.
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department, name;

MySQL historically allowed the invalid form and returned an arbitrary value, which produced quietly wrong reports for years. Modern MySQL enables ONLY_FULL_GROUP_BY by default and errors like everyone else. If you are on an older configuration, turning that mode on is worth doing before it costs you a number somebody trusted.

WHERE against HAVING

Both filter, and they run at different times, which decides both correctness and speed.

WHERE runs before grouping. It filters individual rows, and it cannot reference aggregates because none exist yet.

HAVING runs after grouping. It filters the resulting groups, and it can reference aggregates because that is the whole point.

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE hired_at >= '2020-01-01'     -- drop rows before grouping
GROUP BY department
HAVING COUNT(*) > 5                -- drop groups after grouping
ORDER BY avg_salary DESC;

The performance rule follows directly: filter in WHERE wherever you can. A condition on an individual row belongs there, because rows removed before grouping are rows the engine never has to aggregate, and WHERE can use an index while HAVING cannot.

The common mistake is putting a row-level condition in HAVING because it happens to work:

-- Works, but aggregates every row before discarding most of them.
SELECT department, COUNT(*) FROM employees
GROUP BY department
HAVING department <> 'Contractors';

-- Same result, far less work.
SELECT department, COUNT(*) FROM employees
WHERE department <> 'Contractors'
GROUP BY department;

The three traps that produce wrong numbers

These do not raise errors. They return results that look plausible and are not.

COUNT(*) against COUNT(column). COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. On a nullable column those are different numbers, and using the wrong one silently understates or overstates.

SELECT department,
       COUNT(*)             AS all_rows,
       COUNT(manager_id)    AS rows_with_a_manager,
       COUNT(DISTINCT manager_id) AS distinct_managers
FROM employees
GROUP BY department;

Aggregates ignore NULL, except COUNT(*). AVG(salary) over ten employees where three have no salary recorded divides by seven, not ten. That is usually correct and occasionally very much not, and either way you should know which you are getting. COALESCE makes the decision explicit.

LEFT JOIN plus COUNT(*) inflates zero into one. This is the most common of the three.

-- Wrong: a customer with no orders counts as 1, because the joined row exists
-- with NULL columns and COUNT(*) counts rows.
SELECT c.name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Right: count a column from the joined table, so NULL rows count as zero.
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

The related version of the same problem: joining to two one-to-many tables at once multiplies rows, so your SUM is wrong by a factor nobody notices. When aggregating across multiple joins, aggregate each side separately in a subquery or CTE and join the results.

Grouping by expressions and by time

You can group by any expression, not just a column, and time bucketing is the most common real use.

-- Postgres: monthly revenue.
SELECT date_trunc('month', created_at) AS month,
       SUM(total) AS revenue,
       COUNT(*)   AS orders
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY date_trunc('month', created_at)
ORDER BY month;

Repeating the expression in both SELECT and GROUP BY is the portable form. Postgres and MySQL also accept a column alias or an ordinal position in GROUP BY, which is shorter but less portable:

-- Works in Postgres and MySQL, not everywhere.
SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders GROUP BY month ORDER BY month;

-- Ordinal position: refers to the first SELECT column.
SELECT date_trunc('month', created_at), SUM(total)
FROM orders GROUP BY 1 ORDER BY 1;

One performance note on time grouping. A WHERE clause applying a function to the column, such as WHERE date_trunc('month', created_at) = '2026-01-01', cannot use an ordinary index on that column. Filter on a plain range instead, as in the first example, and let the grouping expression do only the grouping.

The other gap worth knowing: months with no orders do not appear at all, because there are no rows to group. If your chart needs a zero row, generate the series and left join to it.

When a window function is the better tool

GROUP BY collapses rows. Sometimes you want the aggregate alongside every original row, and that is what window functions are for.

-- Each employee, with their department average beside them.
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

No GROUP BY, no collapsing, every row retained with an aggregate attached. Doing this with GROUP BY requires computing the averages separately and joining back, which is more code and usually slower.

Window functions also cover ranking within groups, which GROUP BY cannot express at all:

-- The top three earners in each department.
SELECT * FROM (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn <= 3;

The rule of thumb: if the answer has one row per group, use GROUP BY. If it has one row per original row with group context attached, use a window function. Reaching for the wrong one produces a much more complicated query than necessary.

How this fits the rest of the stack

Aggregate queries are the ones most likely to outgrow a small database plan first, because they read broadly rather than by key and a missing index shows up immediately. The RunxBuild hosting calculator shows the managed Postgres and MySQL ladder next to the service plan, which makes it easier to judge whether a slow report is a query to rewrite or a plan to size up.

Useful related references:

FAQ

Why must every column be in GROUP BY or an aggregate?

Because a group represents many original rows collapsed into one, so a non-grouped column has many possible values and the engine cannot choose. Either add the column to the GROUP BY, wrap it in an aggregate such as MAX or STRING_AGG, or remove it from the SELECT.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot reference aggregates; HAVING filters groups after aggregation and can. Put row-level conditions in WHERE, since rows removed there are never aggregated and WHERE can use an index while HAVING cannot.

Why does COUNT return 1 for records with no matches?

Because COUNT(*) counts rows, and a LEFT JOIN produces a row with NULL columns even when nothing matched. Count a column from the joined table instead, such as COUNT(o.id), which ignores NULLs and correctly returns zero.

Can I group by a column alias?

In PostgreSQL and MySQL, yes, and you can also use the ordinal position of a SELECT column. It is not portable to every engine, so repeating the full expression in the GROUP BY is the safest form when the query may move between databases.

When should I use a window function instead of GROUP BY?

When you want the aggregate alongside every original row rather than collapsing rows into one per group. GROUP BY gives one row per group; a window function with PARTITION BY keeps every row and attaches group context. Ranking within groups requires a window function.