Aggregation Discipline: GROUP BY, HAVING, COUNT

Buckets, not rows — and a precise rule for choosing WHERE over HAVING.

What GROUP BY actually does

GROUP BY sorts rows into buckets, one per distinct value of the grouping key, then runs each aggregate once per bucket. Seven rows in, three department buckets out.

SELECT Department, FirstName, AVG(Salary)
FROM Employees
GROUP BY Department;
-- error: FirstName is not in an aggregate function or the GROUP BY clause

The Technology bucket contains three people. Asking for FirstName gives the engine three candidates and no rule for choosing between them, so it refuses outright.

Rule

Every non-aggregated column in SELECT must appear in GROUP BY. If a single representative value is genuinely wanted, say which one explicitly — MIN(FirstName), or the highest earner via a window function — rather than leaving it to whichever row the engine happens to keep. Older MySQL allowed the ambiguous form and returned an arbitrary row's value; modern defaults reject it, correctly.

Grouping can also use an expression — GROUP BY YEAR(StartDate) — but not the SELECT alias for it, for the same forward-reference reason aliases fail in WHERE. And if GROUP BY produces one row per input row, that's usually a sign DISTINCT or a window function was the actual requirement.

Does GROUP BY guarantee sorted output?

No. Hash-based grouping has no inherent order. If the output needs to be sorted, write ORDER BY explicitly — the same lesson as an unsorted LIMIT, just in a different clause.

Choosing between WHERE and HAVING

-- A: filter before grouping
SELECT Department, COUNT(*) FROM Employees
WHERE Department <> 'Finance'
GROUP BY Department;

-- B: filter after grouping
SELECT Department, COUNT(*) FROM Employees
GROUP BY Department
HAVING Department <> 'Finance';

Both return the same two rows here. Choose A: Department is a plain column, so the filter belongs in WHERE, where it removes rows before the engine does the work of grouping them. B builds the Finance bucket and then throws it away.

Rule

Filter as early as the logic allows. HAVING is mandatory only when the condition itself depends on an aggregate — HAVING COUNT(*) > 2 has no WHERE-clause equivalent, because that value doesn't exist until grouping has happened.

The COUNT family, precisely

COUNT(1) and COUNT(*) produce identical plans in every mainstream engine — the difference is folklore, not tuning advice. And SUM(Salary) / COUNT(*) only equals AVG(Salary) when no salaries are NULL; AVG divides by the count of non-NULL values, not by every row. Watch for integer division truncating the result too — cast before dividing.

Try it

SELECT Department, AVG(Salary + COALESCE(Bonus, 0)) AS AvgTotalPay
FROM Employees
GROUP BY Department
HAVING AVG(Salary + COALESCE(Bonus, 0)) > 60000
ORDER BY AvgTotalPay DESC;