HAVING and advanced aggregations

Filter after aggregation with HAVING and pivot your data with CASE WHEN inside aggregate functions.

8 min · 3 questions

You already know GROUP BY. This module tackles what separates a correct query from a professional one: post-aggregation filtering, conditional aggregates, and a solid grasp of evaluation order. To practice, open sqliteonline.com (SQLite engine) or launch sqlite3 in a terminal: each lesson provides the CREATE TABLE and INSERT statements you need. Copy this dataset before running the examples.

SQL

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  category TEXT NOT NULL,
  amount REAL NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO orders (customer, category, amount, status)
VALUES
  ('alice', 'books', 42.0, 'paid'),
  ('alice', 'games', 55.0, 'paid'),
  ('alice', 'books', 18.5, 'refunded'),
  ('bruno', 'games', 70.0, 'paid'),
  ('bruno', 'books', 12.0, 'paid'),
  ('chloe', 'games', 95.0, 'paid'),
  ('chloe', 'books', 30.0, 'paid'),
  ('david', 'books', 8.0, 'refunded');

Dataset for this lesson, run it first.

The logical order of evaluation is the key: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups after the aggregates are computed. That is why WHERE SUM(amount) > 90 is illegal: at the WHERE stage, no sum exists yet. Here, we keep only customers whose paid total exceeds 90 (expected: alice and chloe).

SQL

SELECT customer,
       COUNT(*) AS nb_orders,
       SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 90;

WHERE removes rows, HAVING removes groups.

A classic performance trap: putting a non-aggregate condition in HAVING, like HAVING status = 'paid'. The engine then aggregates every row before discarding entire groups. A condition on a raw column belongs in WHERE, which shrinks the volume before grouping.

The second must-know pattern is the conditional aggregate. By slipping a CASE WHEN inside SUM or COUNT, you compute several metrics in a single pass over the table, where a beginner would run three separate queries. This is the standard technique for pivoting rows into columns.

SQL

SELECT customer,
  SUM(CASE WHEN category = 'books'
      THEN amount ELSE 0 END) AS books_total,
  SUM(CASE WHEN category = 'games'
      THEN amount ELSE 0 END) AS games_total
FROM orders
WHERE status = 'paid'
GROUP BY customer;

Pivot rows → columns in a single read.

SQLite (≥ 3.30) and PostgreSQL offer a more readable syntax: the FILTER clause, for example COUNT(*) FILTER (WHERE status = 'refunded'). It exists in neither MySQL nor SQL Server: stick with CASE WHEN for portable code. Another SQLite shortcut: a boolean is 0 or 1, so SUM(status = 'refunded') counts the refunds.

Knowledge check

Make sure you remember the key points of this lesson.

In what logical order does the engine evaluate these clauses?
- WHERE → GROUP BY → HAVING
- HAVING → WHERE → GROUP BY
- GROUP BY → HAVING → WHERE
Which condition should stay in WHERE rather than HAVING?
- SUM(amount) > 100
- status = 'paid'
- COUNT(*) >= 2
What does SUM(CASE WHEN category = 'books' THEN amount ELSE 0 END) compute?
- The total of all orders
- The total of only the rows in the 'books' category
- The number of rows in the 'books' category
- The total of rows outside 'books'