SQLite HAVING

The HAVING clause filters groups after GROUP BY runs. WHERE filters individual rows before grouping; HAVING filters the resulting groups after aggregation. You need HAVING whenever a condition depends on an aggregate value like SUM, COUNT, or AVG.

WHERE vs HAVING — The Key Difference

WHERE  → filters ROWS     (before grouping)
HAVING → filters GROUPS   (after aggregation)

Timeline of a query:
──────────────────────────────────────────────────────
1. FROM       Load all rows
2. WHERE      ← filter individual rows here
3. GROUP BY   Form groups from remaining rows
4. Aggregate  Calculate COUNT, SUM, AVG etc per group
5. HAVING     ← filter groups here
6. SELECT     Choose columns to display
7. ORDER BY   Sort results

Sample Table

sales
──────────────────────────────────────────────
id │ rep    │ region │ product  │ amount
───┼────────┼────────┼──────────┼────────
1  │ Alice  │ North  │ Laptop   │ 1200
2  │ Bob    │ South  │ Phone    │ 800
3  │ Alice  │ North  │ Tablet   │ 500
4  │ Carol  │ East   │ Laptop   │ 1100
5  │ Bob    │ South  │ Laptop   │ 1300
6  │ Carol  │ East   │ Phone    │ 750
7  │ Alice  │ West   │ Phone    │ 900
8  │ Dan    │ North  │ Tablet   │ 400
9  │ Dan    │ East   │ Phone    │ 600

Basic HAVING Example

-- Only show reps whose total sales exceed 2000
SELECT rep, SUM(amount) AS total
FROM sales
GROUP BY rep
HAVING SUM(amount) > 2000;

rep   │ total
──────┼───────
Alice │ 2600
Bob   │ 2100
Carol │ 1850  ← excluded (1850 < 2000... wait)

Let's verify:
Alice: 1200+500+900 = 2600 ✓
Bob:   800+1300     = 2100 ✓
Carol: 1100+750     = 1850 ✗ (below 2000)
Dan:   400+600      = 1000 ✗

HAVING with COUNT

-- Only reps who closed more than 2 deals
SELECT rep, COUNT(*) AS deals
FROM sales
GROUP BY rep
HAVING COUNT(*) > 2;

rep   │ deals
──────┼───────
Alice │ 3
Dan   │ 2    ← excluded (not > 2)

Alice has 3 deals (rows 1, 3, 7): included ✓
Bob has 2 deals (rows 2, 5): excluded ✗
Carol has 2 deals (rows 4, 6): excluded ✗
Dan has 2 deals (rows 8, 9): excluded ✗

HAVING with AVG

-- Reps whose average deal size is above 800
SELECT rep, ROUND(AVG(amount), 2) AS avg_deal
FROM sales
GROUP BY rep
HAVING AVG(amount) > 800;

rep   │ avg_deal
──────┼──────────
Alice │ 866.67   (2600/3)
Bob   │ 1050.00  (2100/2)
Carol │ 925.00   (1850/2)

Dan: 500.00 (1000/2) → excluded ✗

HAVING with WHERE Together

WHERE and HAVING can work in the same query. WHERE pre-filters rows before grouping; HAVING post-filters the formed groups.

-- Among North and South regions only,
-- show reps with total sales above 1000

SELECT rep, region, SUM(amount) AS total
FROM sales
WHERE region IN ('North', 'South')   ← filter rows first
GROUP BY rep
HAVING SUM(amount) > 1000;           ← filter groups after

Step-by-step:
1. WHERE keeps only North + South rows:
   Alice/North: 1200, 500 → included
   Bob/South:   800, 1300 → included
   Dan/North:   400       → included

2. GROUP BY rep:
   Alice total: 1700
   Bob total:   2100
   Dan total:   400

3. HAVING > 1000:
   Alice: 1700 ✓
   Bob:   2100 ✓
   Dan:   400  ✗

rep   │ region │ total
──────┼────────┼───────
Alice │ North  │ 1700
Bob   │ South  │ 2100

HAVING vs WHERE — When Each Applies

SCENARIO                              USE
──────────────────────────────────────────────────────────
Filter by a column value              WHERE
  e.g. only North region rows         WHERE region='North'

Filter by aggregate result            HAVING
  e.g. only groups with SUM > 1000    HAVING SUM(amount)>1000

Condition does not involve            WHERE (faster)
  an aggregate function

Condition involves COUNT,             HAVING (required)
  SUM, AVG, MIN, or MAX

HAVING Without GROUP BY

HAVING without GROUP BY treats the entire table as one group and filters that single group.

-- Only return results if total revenue exceeds 5000
SELECT SUM(amount) AS grand_total
FROM sales
HAVING SUM(amount) > 5000;

grand_total: 7550   ← shown because 7550 > 5000

-- If total were less than 5000, no rows would appear at all.

Practical Example: Sales Leaderboard

-- Top performers: more than 1 deal and average above 700
SELECT
  rep,
  COUNT(*)             AS deals,
  SUM(amount)          AS total_revenue,
  ROUND(AVG(amount),2) AS avg_deal
FROM sales
GROUP BY rep
HAVING COUNT(*) > 1 AND AVG(amount) > 700
ORDER BY total_revenue DESC;

rep   │ deals │ total_revenue │ avg_deal
──────┼───────┼───────────────┼──────────
Alice │ 3     │ 2600          │ 866.67
Bob   │ 2     │ 2100          │ 1050.00
Carol │ 2     │ 1850          │ 925.00

Key Takeaways

HAVING filters groups; WHERE filters individual rows
HAVING runs after GROUP BY and aggregation
Use HAVING when the condition involves COUNT, SUM, AVG, MIN, or MAX
WHERE and HAVING can coexist in one query — they operate at different stages
HAVING without GROUP BY applies to the entire table as a single group

Previous lesson

Back to course

Next lesson