Aggregates

Having

Filtering Groups

HAVING filters after grouping. It can use aggregate values that WHERE cannot.

Program

Play the query to keep only items with at least five sold.

having.sql
Replay: real traced execution (multi-file project)
CREATE TABLE sales (item TEXT, qty INTEGER);
INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3), ('pencil', 1);
SELECT item, SUM(qty) AS sold FROM sales GROUP BY item HAVING SUM(qty) >= 5 ORDER BY item;

  1. tables ← 1 row

    1CREATE TABLE sales (item TEXT, qty INTEGER);2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3), ('pencil', 1);
    values this step1 rowtables

  2. sales ← 4 rows

    1CREATE TABLE sales (item TEXT, qty INTEGER);2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3), ('pencil', 1);3SELECT item, SUM(qty) AS sold FROM sales GROUP BY item HAVING SUM(qty) >= 5 ORDER BY item;
    values this step4 rowssales

  3. result ← 2 rows

    2INSERT INTO sales VALUES ('book', 2), ('pen', 5), ('book', 3), ('pencil', 1);3SELECT item, SUM(qty) AS sold FROM sales GROUP BY item HAVING SUM(qty) >= 5 ORDER BY item;
    values this step2 rowsresult

Filter After Totals

  1. Group rows by item.
  2. Sum each group's qty.
  3. HAVING SUM(qty) >= 5 keeps only groups with totals at least 5.
  4. pencil is removed because its group total is 1. | Item group | Sold | Kept? | | --- | --- | --- | | book | 5 | yes | | pen | 5 | yes | | pencil | 1 | no |
HAVING `HAVING SUM(qty) >= 5` keeps groups after aggregation.
WHERE vs HAVING `WHERE` filters rows before grouping; `HAVING` filters groups after grouping.
threshold Aggregate filters are useful for leaderboards and reports.

Exercise: having.sql

Group sales by item, keep only groups with at least five sold, and sort the kept rows