HAVING

Filtering Groups, Not Rows

HAVING filters groups produced by GROUP BY, evaluated after aggregation. This is the key distinction from WHERE, which filters individual rows before grouping happens (Chapter 8/12 introduced WHERE; Topic 15.3 contrasts the two directly).

Because HAVING runs after aggregates are computed, it is the only clause allowed to reference an aggregate function directly in its condition.

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Basic Example

sql
SELECT region, SUM(amount) AS total_amount
FROM orders
GROUP BY region
HAVING SUM(amount) > 400
ORDER BY region;

Per-region totals are East = 310, North = 200, West = 550. Only West clears the 400 bar:

Multi-Branch (Compound) HAVING

HAVING supports AND / OR just like WHERE, combining multiple aggregate conditions:

sql
SELECT region,
       COUNT(*)    AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS avg_amount
FROM orders
GROUP BY region
HAVING COUNT(*) > 2 AND AVG(amount) > 100
ORDER BY region;

Evaluating each group:

• East: count = 3 (>2 ✓), avg = 103.33 (>100 ✓) → passes
• North: count = 2 (>2 ✗) → fails
• West: count = 3 (>2 ✓), avg = 183.33 (>100 ✓) → passes

North is dropped even though it was a valid group — HAVING removes whole groups that fail the condition, the same way WHERE removes whole rows.

HAVING Without GROUP BY

HAVING can technically be used without an explicit GROUP BY — the entire table is then treated as one implicit group:

sql
SELECT SUM(amount) AS total_amount
FROM orders
HAVING SUM(amount) > 5000;   -- 1060 is not > 5000, so 0 rows returned

Edge Cases

• HAVING referencing a non-aggregated, non-grouped column: rejected for the same reason as in SELECT (Topic 15.1) — the column has no single value per group unless it's part of GROUP BY.
• HAVING with an alias: PostgreSQL and MySQL allow HAVING total_amount > 400 using the SELECT-list alias; strict standard SQL technically does not guarantee this, so HAVING SUM(amount) > 400 is the more portable form.
• Using HAVING for row-level filtering: legal but wasteful — e.g. HAVING region = 'West' still works, but the engine aggregates all rows first, then discards groups. See 15.3 for why this is inefficient compared to WHERE.

Key Takeaways / Interview Q&A

• Q: Can HAVING reference an aggregate function?

A: Yes — that's its entire purpose; WHERE cannot.

• Q: In the orders example, which regions pass `HAVING SUM(amount) > 400`?

A: Only West (550); East (310) and North (200) do not. A: (compound example) East and West pass HAVING COUNT(*) > 2 AND AVG(amount) > 100; North fails on COUNT(*).

• Q: Does HAVING run before or after GROUP BY?

A: After — GROUP BY forms the groups and computes aggregates first, then HAVING filters which groups survive.

• Q: Is `GROUP BY` mandatory to use `HAVING`?

A: No, but without it the whole table is one implicit group.