Ravindra BagaleCourses & study guides Track your progress

13. HAVING: Filter Groups After Aggregates

13.3 How to Combine WHERE (Row Filter) and HAVING (Group Filter)

By Last updated:

Why. Real questions often need both filters, such as deposits only, and only branches with enough of them.

What. Put the row condition in WHERE and the group condition in HAVING. WHERE runs first, so HAVING only sees totals made from the rows that WHERE kept.

How. Count deposits per branch with WHERE, then keep branches with at least 3 deposits using HAVING.

Steps

  1. Run the first example, which uses both WHERE and HAVING.
  2. Run the second example, which has HAVING only, and compare the counts.
  3. See that the counts change, because the second query counts every type of transaction.
  4. Decide which question your manager really asked before you choose.

Examples

Example 1 · dataset: northstar_bank

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

Output

branch_iddeposits
23
33
44

Meaning. Only branches with 3 or more deposits are shown. Branches with fewer deposits were dropped by HAVING.

Example 2 · dataset: northstar_bank

SELECT branch_id, COUNT(*) AS all_transactions
FROM transactions
GROUP BY branch_id
HAVING COUNT(*) >= 3
ORDER BY branch_id;

Output

branch_idall_transactions
16
27
36
46

Meaning. Without WHERE, every branch passes, because cheques and withdrawals are counted too. This answers a different question.

Ravindra Bagale's Tip

In a grouped query, if a condition checks single rows and does not use SUM, COUNT, AVG, MIN or MAX, put it in WHERE. The query is clearer and does less work.