HAVING: Filtering the Groups Themselves

WHERE filters rows, HAVING filters buckets

Suppose you want only the customers who spent more than $10. You cannot write WHERE SUM(price) > 10: WHERE runs before grouping, when no sums exist yet. Filtering on an aggregate needs HAVING, which runs after the buckets are built:

SELECT customer, SUM(price) AS total
FROM orders
GROUP BY customer
HAVING SUM(price) > 10
ORDER BY customer;

Two new things here:

• AS total gives the computed column a readable name (an alias).
• The full clause order is fixed: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Rule of thumb: a test about a single row (its price, its item) belongs in WHERE. A test about a whole group (its total, its count) belongs in HAVING.

Dropping the small buckets

HAVING tests each finished bucket. Cara spent only 5, so her bucket fails the test and disappears from the output, leaving two rows.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT customer, SUM(price) AS total
FROM orders
GROUP BY customer
HAVING SUM(price) > 10
ORDER BY customer;

Output

Ana|17
Ben|13

All three buckets were built and all three sums were computed. HAVING then threw one away, which is a useful mental picture: it filters results, not inputs, so it can never save the database any counting work.

The alias AS total renames the computed column. Many databases will also let HAVING total > 10 refer to that alias, but repeating SUM(price) is the portable spelling and works everywhere.

Placing a row-level test

For counting the coffees each customer bought, the test item = 'coffee' belongs in WHERE.

It examines one row's item value, not a group total, so it is a row-level test by nature. Placement also changes the answer: WHERE removes the non-coffee rows before the buckets are built, while HAVING would count every order first and then discard whole customers.

The two occasionally agree by coincidence on small data, which is precisely what makes the confusion survive so long.

Items ordered at least twice

"At least twice" is a statement about a bucket's count, which puts it in HAVING territory.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT item, COUNT(*) AS times_ordered
FROM orders
GROUP BY item
HAVING COUNT(*) >= 2
ORDER BY item;

Output

coffee|3
sandwich|2

The HAVING clause sits between GROUP BY and ORDER BY, which is the only place SQL accepts it. Bagel was ordered once, so its bucket fails COUNT(*) >= 2 and never reaches the output.

This shape is how real systems find duplicates. Grouping by an email address and keeping the buckets with COUNT(*) > 1 is the standard query for locating accounts that were created twice.

The order SQL requires

Written in the order SQL demands, the four clauses are WHERE, GROUP BY, HAVING, ORDER BY.

That sequence mirrors what the database actually does: filter rows, build buckets, filter buckets, then sort the final output. WHERE has to run while rows still exist individually, HAVING cannot run until GROUP BY has produced buckets for it to test, and ORDER BY sorts whatever survives, so it comes last.

The full skeleton, with the two clauses from earlier units back in place:

SELECT columns
FROM table
WHERE row_test
GROUP BY column
HAVING group_test
ORDER BY column
LIMIT n;

Getting the order wrong is a syntax error rather than a silent bug, which is one of the friendlier things about SQL.