Ravindra BagaleCourses & study guides Track your progress

13. HAVING: Filter Groups After Aggregates

13.1 WHERE vs HAVING in One Picture

By Last updated:

Why. WHERE and HAVING both filter, so beginners mix them up. Knowing which one runs when keeps your totals correct.

What. SQLite reads a query in this order: FROM, then WHERE on single rows, then GROUP BY, then HAVING on whole groups. WHERE cannot use SUM or COUNT, because the groups do not exist yet. HAVING can, because it runs after the totals are made.

How. Use WHERE to keep only deposits, and HAVING to keep only accounts with at least 4 transactions.

Steps

  1. Run the first example: WHERE removes rows before grouping.
  2. Run the second example: HAVING removes groups after counting.
  3. Read the picture: rows, then WHERE, then groups, then HAVING, then the result.
  4. Remember: in a grouped query like this one, a condition on a group's SUM or COUNT goes in HAVING, and a condition on single rows goes in WHERE.

Examples

Example 1 · dataset: northstar_bank

SELECT branch_id, COUNT(*) AS deposits
FROM transactions
WHERE txn_type = 'deposit'
GROUP BY branch_id
ORDER BY branch_id;

Output

branch_iddeposits
12
23
33
44

Meaning. WHERE kept only deposit rows, and then GROUP BY counted them for each branch.

Example 2 · dataset: northstar_bank

SELECT account_id, COUNT(*) AS transactions
FROM transactions
GROUP BY account_id
HAVING COUNT(*) >= 4
ORDER BY account_id;

Output

account_idtransactions
1034

Meaning. Only account 103 has 4 or more transactions, so HAVING kept just that one group. The other seven accounts were counted first and then dropped.

One picture
  1. All rows from FROM
  2. WHERE keeps matching rows
  3. GROUP BY makes groups
  4. HAVING keeps matching groups
  5. SELECT shows the result

Ravindra Bagale's Tip

If you write WHERE SUM(amount) > 1000, SQLite gives an error about misuse of an aggregate. That error means: move this condition to HAVING.