SQL · Guides · October 29, 2026 · 10 min read

GROUP BY vs HAVING (and WHERE vs HAVING) in SQL, with examples

GROUP BY and HAVING are not alternatives: they do two different jobs in the same query. GROUP BY collapses rows into groups (one output row per airport, per day, per class) so that aggregates like COUNT and AVG are computed per group. HAVING filters those groups after the aggregates are known. WHERE is the third piece: it filters individual rows before any grouping happens. So the short rule is: a condition on a raw column goes in WHERE; a condition on an aggregate (COUNT(*) > 1000, AVG(amount) > 300) goes in HAVING.

All examples below run on the airline database used in the Nomto SQL course: 33,121 flights between 104 airports in summer 2026, with bookings, tickets and flight segments. Money is in US dollars. You can paste every query into the Nomto SQL room and check the output yourself.

What GROUP BY does

Without GROUP BY, an aggregate turns the whole table into one row. With it, you get one row per distinct value of the grouping columns.

SELECT status, COUNT(*) AS flights
FROM flights
GROUP BY status
ORDER BY flights DESC;

Six statuses, six rows. Each row is a group, and COUNT(*) was computed separately inside each group.

You can group by several columns, and by expressions. Here is a count of seats per aircraft model, with a conditional count of business seats in the same pass:

SELECT aircraft_code,
       COUNT(*) AS seats,
       SUM(CASE WHEN fare_conditions = 'Business' THEN 1 ELSE 0 END) AS business
FROM seats
GROUP BY aircraft_code
ORDER BY seats DESC;

(The ninth model, CN1, has 12 seats and no business class.)

What HAVING does

HAVING keeps or drops whole groups. It is evaluated after the aggregates exist, so it can refer to them.

SELECT departure_airport, COUNT(*) AS departures
FROM flights
WHERE status = 'Arrived'
GROUP BY departure_airport
HAVING COUNT(*) > 1000
ORDER BY departures DESC;

Read it in the order the database does: take all flights, keep only the ones that arrived (WHERE), group them by departure airport, count each group, keep only groups with more than 1,000 rows (HAVING). Two Moscow airports, Domodedovo and Sheremetyevo, pass.

HAVING can use any aggregate, not just the one you select. Which aircraft models have no business class at all?

SELECT aircraft_code, COUNT(*) AS seats
FROM seats
GROUP BY aircraft_code
HAVING SUM(CASE WHEN fare_conditions = 'Business' THEN 1 ELSE 0 END) = 0;

The Bombardier CRJ-200 and the Cessna 208 Caravan.

WHERE vs HAVING: the difference

The practical consequence: a WHERE condition changes what goes into the aggregates; a HAVING condition does not. Compare these two queries on segment prices.

Average price per class, keeping only segments that cost more than $300:

SELECT fare_conditions, COUNT(*) AS segments, ROUND(AVG(amount), 2) AS avg_price
FROM ticket_flights
WHERE amount > 300
GROUP BY fare_conditions
ORDER BY avg_price DESC;

Average price per class, keeping only classes whose average is above $300:

SELECT fare_conditions, COUNT(*) AS segments, ROUND(AVG(amount), 2) AS avg_price
FROM ticket_flights
GROUP BY fare_conditions
HAVING AVG(amount) > 300
ORDER BY avg_price DESC;

The first query answers “what does an expensive segment cost on average in each class”; every average is inflated because cheap segments were thrown away, and Economy even overtakes Comfort. The second answers “which classes are expensive on average”; the averages are the true ones (Economy’s is $159.60, so it drops out). Same columns, same number, completely different questions.

When both would work, use WHERE

A condition on a grouped column can technically go in either place:

SELECT departure_airport, COUNT(*) AS departures
FROM flights
WHERE departure_airport IN ('SVO', 'DME', 'VKO')
GROUP BY departure_airport
ORDER BY departures DESC;

Moving the condition to HAVING departure_airport IN ('SVO', 'DME', 'VKO') returns the same three rows. But the HAVING version first groups and counts all 104 airports and then throws 101 groups away. Many optimizers push such a condition down for you, but you should not rely on it, and WHERE also tells the reader your intent: these rows are out of scope from the start.

The order SQL runs a query in

You write SELECT first, but the database evaluates the clauses in this logical order:

1. FROM and JOIN: build the set of rows
2. WHERE: drop rows
3. GROUP BY: form groups
4. aggregates are computed per group
5. HAVING: drop groups
6. SELECT: compute output columns and aliases
7. DISTINCT
8. ORDER BY
9. LIMIT / OFFSET

Almost every GROUP BY / HAVING error follows from this list:

• WHERE runs before aggregates exist, so WHERE COUNT(*) > 1000 is impossible.
• SELECT runs after GROUP BY, so each selected column must have one value per group.
• ORDER BY runs after SELECT, so it can use a column alias such as departures.
• In PostgreSQL, HAVING runs before SELECT, so it cannot use a select alias: write HAVING COUNT(*) > 1000, not HAVING departures > 1000. Some engines (MySQL, DuckDB) accept the alias, which is why code copied between databases sometimes breaks here.

Common mistakes

1. An aggregate in WHERE

SELECT departure_airport, COUNT(*)
FROM flights
WHERE COUNT(*) > 1000
GROUP BY departure_airport;

This fails. PostgreSQL reports “aggregate functions are not allowed in WHERE”. Move the condition to HAVING.

2. A selected column that is neither grouped nor aggregated

SELECT departure_airport, arrival_airport, COUNT(*)
FROM flights
GROUP BY departure_airport;

This also fails: “column arrival_airport must appear in the GROUP BY clause or be used in an aggregate function”. One departure airport has many arrival airports, so the database cannot pick one value per group. Either add arrival_airport to GROUP BY (one row per route) or aggregate it, e.g. COUNT(DISTINCT arrival_airport).

PostgreSQL makes one exception: if you group by a table’s primary key, you may select other columns of that table. Grouping by b.book_ref lets you select b.total_amount. Listing both in GROUP BY works everywhere, so the queries here do that:

SELECT b.book_ref, COUNT(*) AS tickets, b.total_amount
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref
GROUP BY b.book_ref, b.total_amount
HAVING COUNT(*) >= 5
ORDER BY b.total_amount DESC
LIMIT 5;

Only 23 bookings in the whole database have five or more tickets.

3. Ranking small groups next to big ones

Average departure delay by airport, among flights that actually took off:

SELECT departure_airport,
       ROUND(AVG(EXTRACT(EPOCH FROM actual_departure - scheduled_departure) / 60), 1) AS avg_delay_min,
       COUNT(*) AS departed
FROM flights
WHERE status IN ('Arrived', 'Departed')
GROUP BY departure_airport
ORDER BY avg_delay_min DESC
LIMIT 3;

The “worst” airports have 22 to 97 departures, so a couple of long delays dominate their averages. A minimum group size in HAVING makes the comparison fairer:

SELECT departure_airport,
       COUNT(*) AS departed,
       ROUND(AVG(EXTRACT(EPOCH FROM actual_departure - scheduled_departure) / 60), 1) AS avg_delay_min
FROM flights
WHERE status IN ('Arrived', 'Departed')
GROUP BY departure_airport
HAVING COUNT(*) >= 300
ORDER BY avg_delay_min DESC
LIMIT 5;

Note the division of labour: WHERE decides which flights count as departed, HAVING decides which airports have enough of them.

4. COUNT(*) where you meant COUNT(column)

COUNT(*) counts rows; COUNT(column) counts non-NULL values. In a grouped query the difference is often the whole point:

SELECT status, COUNT(*) AS flights, COUNT(actual_departure) AS with_departure
FROM flights
GROUP BY status
ORDER BY flights DESC;

Eight cancelled flights have a recorded departure time, a quirk of the data you would never notice with COUNT(*) alone. A HAVING COUNT(actual_departure) > 0 on this query would keep three groups, not six.

5. Grouping by an expression in one place and not the other

If you group by a date, group by the same expression you select:

SELECT CAST(scheduled_departure AS date) AS day, COUNT(*) AS cancelled
FROM flights
WHERE status = 'Cancelled'
GROUP BY CAST(scheduled_departure AS date)
HAVING COUNT(*) >= 15
ORDER BY day;

Grouping by the raw scheduled_departure timestamp would give one group per departure minute, and the HAVING would find nothing. (The pile-up at the very end of the schedule is worth a second look before anyone reports it as a crisis: 403 of the 414 cancellations sit in the future September schedule.)

HAVING without GROUP BY

If a query has aggregates and HAVING but no GROUP BY, the whole result is treated as one group. The query returns one row or none:

SELECT COUNT(*) AS flights
FROM flights
HAVING COUNT(*) > 30000;

Change the threshold to 40,000 and you get zero rows. This is occasionally useful in data checks: a query that returns a row only when something is wrong.

A quick decision checklist

• Does the condition use COUNT, SUM, AVG, MIN, MAX or another aggregate? Put it in HAVING.
• Is it about a single row’s values (status, date, price of one segment)? Put it in WHERE.
• Is it about a grouped column only? It works in both; prefer WHERE.
• Does every column in SELECT appear in GROUP BY or inside an aggregate? If not, the query is wrong.
• In PostgreSQL, did you repeat the aggregate in HAVING rather than using its alias?

If joins are involved, check row multiplication before you trust any SUM or COUNT; the SQL joins guide covers that. When you need an aggregate next to each row instead of collapsing rows, you want window functions, not GROUP BY.

FAQ

Can I use HAVING without GROUP BY?

Yes. The whole result becomes one group, and the query returns either one row or nothing, depending on the HAVING condition.

Is HAVING slower than WHERE?

For conditions on aggregates there is no choice, so speed is not the question. For conditions on raw columns, WHERE lets the database discard rows before grouping, which is never slower and often faster. Use WHERE whenever the condition does not need an aggregate.

Can I use a column alias in HAVING?

Not in PostgreSQL or standard SQL: HAVING is evaluated before SELECT, so the alias does not exist yet. Repeat the expression. MySQL and some other engines allow the alias as an extension.

Can WHERE and HAVING be used in the same query?

Yes, and they often are: WHERE picks the rows that feed the aggregates, HAVING picks the groups to keep. The departures and delays examples above use both.

What is the difference between GROUP BY and DISTINCT?

SELECT DISTINCT status FROM flights and SELECT status FROM flights GROUP BY status return the same six values. GROUP BY additionally lets you compute aggregates per group and filter groups with HAVING; DISTINCT only removes duplicate rows.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文