GROUP BY and HAVING

The clauses do not run in the order you write them

SQL is written SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, and evaluated in a different order entirely:

FROM      →  which rows exist
WHERE     →  throw rows away
GROUP BY  →  fold the survivors into groups
HAVING    →  throw groups away
SELECT    →  compute the output columns
ORDER BY  →  sort what is left

Almost every confusing error about grouping comes from that list. WHERE cannot see an aggregate, because no group exists yet when it runs. HAVING can, because it runs after. And an alias defined in SELECT is not usable in WHERE — it does not exist yet either.

WHERE filters rows, HAVING filters groups

SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'paid'          -- rows: only paid orders count
GROUP BY customer_id
HAVING SUM(total) > 1000;      -- groups: only big spenders survive

Swap them and the query is either wrong or refuses to run. WHERE SUM(total) > 1000 is an error. HAVING status = 'paid' is worse in some engines — it runs, after the sum has already included the unpaid orders.

The rule: if the condition is about a single row, it is `WHERE`. If it needs the whole group, it is `HAVING`. Put everything you can in WHERE: fewer rows reach the grouping, and the query is faster for the same answer.

Every selected column must be grouped or aggregated

-- Error in a standards-compliant engine
SELECT department_id, name, COUNT(*)
FROM employees
GROUP BY department_id;

The group is a department; name is a property of one employee, and the engine has no way to choose which. MySQL historically returned an arbitrary one, which is how this became a habit people carry between databases. If you want a representative value, say which one:

SELECT department_id, MIN(name) AS first_name_alphabetically, COUNT(*)
FROM employees
GROUP BY department_id;

COUNT(*) and COUNT(column) are different questions

That second line is a quiet source of wrong dashboards:

SELECT COUNT(*) AS employees,
       COUNT(manager_id) AS employees_with_a_manager
FROM employees;

Same table, two different numbers, and the difference is exactly the number of NULLs.

Counting rows that match a condition

The portable trick, worth knowing because it lets one pass produce several numbers:

SELECT
  COUNT(*) AS orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders;

Three counts, one scan of the table. The alternative — three queries, or three subqueries — reads the same rows three times.

A group that does not exist produces no row

SELECT status, COUNT(*)
FROM orders
GROUP BY status;

If no order was ever refunded, there is no refunded row in the result — not a row with zero. Grouping can only fold rows that exist. When a report needs every status present, the statuses have to come from somewhere else: a lookup table, joined LEFT, with the orders on the right.