Skip to content

Beginner: asking questions · Aggregation: GROUP BY and HAVING · topic 18 of 62 · 30 min

HAVING versus WHERE

Explanation

WHERE filters single rows before they are grouped; HAVING filters whole groups after the aggregates are worked out. So "students with marks of 60 or more" is WHERE, and "cities with more than 1 student" is HAVING. A query can use both, and the logical order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT explains which goes where.

Why two filters?

Suppose the college wants the cities that have more than one student. WHERE cannot do it: WHERE looks at one row at a time, and a single row does not know how many other students share its city. The count only exists after grouping. HAVING is the filter that runs after grouping, so it can test aggregates like COUNT(*) > 1.

sql
SELECT city, COUNT(*) AS students
FROM students
GROUP BY city
HAVING COUNT(*) > 1;
-- Delhi | 2
-- Pune  | 3
sql
SELECT city, COUNT(*) FROM students WHERE COUNT(*) > 1 GROUP BY city;
-- Error: misuse of aggregate: COUNT()
An aggregate cannot go in WHERE, because WHERE runs before the groups exist.

The logical order, now complete

StepClauseWorks on
1FROMThe table(s)
2WHERESingle rows: keeps rows that pass
3GROUP BYPuts the remaining rows into groups
4HAVINGWhole groups: keeps groups that pass
5SELECTWorks out columns and aggregates; aliases are born
6ORDER BYSorts the result rows
7LIMIT / OFFSETKeeps some of them
Think of it like this: Think of a school sports day. WHERE is the gate check: only students with a valid ID enter the ground. GROUP BY puts them into house teams. HAVING drops any team with fewer than five players. SELECT writes the score sheet, ORDER BY ranks it, LIMIT keeps the top three for the prize.

Using both in one query

WHERE and HAVING answer different questions, and the results can be very different. "Cities with more than one student" gives Delhi and Pune. "Cities with more than one student who scored 60 or more" first removes the low scorers with WHERE, then counts:

sql
SELECT city, COUNT(*) AS passed
FROM students
WHERE marks >= 60
GROUP BY city
HAVING COUNT(*) > 1;
-- Delhi | 2
Pune drops out: only Asha (76) passes there; Imran (58) and Sneha (NULL) are removed by WHERE.
sql
SELECT dept_id, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 90000;
-- 1 | 140000
Only Engineering averages more than 90000 a month.

Filter rows with WHERE when you can

You could write HAVING status <> 'Cancelled' after GROUP BY status, and SQLite would accept it, because status is a grouped column. But it is clearer, and usually faster, to remove rows with WHERE before grouping. Rule of thumb: a condition on a plain column goes in WHERE; a condition on an aggregate goes in HAVING.

Aliases in HAVING

HAVING runs before SELECT in the logical order, so standard SQL does not let it use a SELECT alias. SQLite and MySQL accept HAVING students > 1; PostgreSQL and SQL Server reject it. Repeat the aggregate, HAVING COUNT(*) > 1, so the query works everywhere.

Common mistakes

  • Putting an aggregate in WHERE. Move the condition to HAVING.
  • Putting a plain row condition in HAVING when it belongs in WHERE. It works in some cases but is slower and confusing.
  • Mixing up the question: "customers with 2 or more orders" (HAVING COUNT(*) >= 2) is not "orders worth 2000 or more" (a row condition, or HAVING SUM(...) for order totals).
  • Using a SELECT alias in HAVING and then moving to PostgreSQL. Repeat the aggregate.
  • Writing HAVING before GROUP BY. The written order is WHERE, GROUP BY, HAVING, ORDER BY.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

HAVING versus WHERE · 1/7

HAVING versus WHERE

Two filters, two jobs. WHERE checks single rows. HAVING checks whole groups.

Prefer a video by a teacher? Find free videos on “sql having vs where difference group by tutorial” on YouTube ↗

In real life: Finding repeat customers

A food delivery or shopping app often wants its repeat customers, for example everyone with two or more completed orders, to send them a loyalty offer. Cancelled orders should not count, so they are removed with WHERE; then the orders are grouped per customer and HAVING keeps only those with enough orders.

sql
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY orders DESC;
Customer 1 (3 orders) and customer 2 (2 orders).

Practice (0/4 done)

Task 1 · Code · SQL

Which cities have more than 1 student? Return city and the number of students, named students.

Task 2 · Code · SQL

Which departments pay an average monthly salary over 90000? From employees (COMPANY), return dept_id and the average salary rounded to 2 decimal places, named avg_salary.

Task 3 · Code · SQL

Which customers have placed at least 2 orders (any status)? From orders, return customer_id and the number of orders, named orders.

Task 4 · Code · SQL

Count only students with marks of 60 or more. Which cities have more than 1 such student? Return city and the count, named passed.

Quick quiz

Question 1

1.Where does the condition "average salary above 90000" go?

Question 2

2.Where does the condition "only orders that are not cancelled" go, when counting orders per customer?

Question 3

3.In the logical order, which runs first?

Question 4

4.SELECT city, COUNT(*) AS n FROM students GROUP BY city HAVING n > 1 works in SQLite. What happens in PostgreSQL?

Question 5

5.What does WHERE COUNT(*) > 1 give?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

Where does the condition "average salary above 90000" go?

Answer: HAVING

It tests an aggregate, which exists only after grouping.

Where does the condition "only orders that are not cancelled" go, when counting orders per customer?

Answer: WHERE, before grouping

It is a condition on single rows; remove them before they are grouped.

In the logical order, which runs first?

Answer: WHERE

FROM, then WHERE, then GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

SELECT city, COUNT(*) AS n FROM students GROUP BY city HAVING n > 1 works in SQLite. What happens in PostgreSQL?

Answer: It fails, because a SELECT alias is not visible in HAVING

HAVING runs before SELECT in standard SQL. Write HAVING COUNT(*) > 1.

What does WHERE COUNT(*) > 1 give?

Answer: An error: aggregates are not allowed in WHERE

WHERE runs before groups exist; use HAVING.

Why does PostgreSQL reject SELECT city, name, SUM(amount) FROM t GROUP BY city?Related

Answer: name is neither grouped nor aggregated, so one city has many possible names

Each output row is one group. A column must be the same for the whole group (grouped) or combined from it (aggregated).

From GROUP BY and HAVING

students has 8 rows in 5 different cities. How many rows does SELECT city, COUNT(*) FROM students GROUP BY city return?Related

Answer: 5

GROUP BY gives one row per group, and there are 5 cities.

From GROUP BY

In which logical order are these clauses worked out?Related

Answer: FROM, WHERE, SELECT, ORDER BY

FROM picks the table, WHERE filters, SELECT builds columns, ORDER BY sorts.

From DISTINCT and how a query runs

What does SELECT city, SUM(amount) FROM orders GROUP BY city return?Related

Answer: One row per city with its total

GROUP BY city makes one group per city, and SUM runs on each group.

From Counting and totals: aggregate functions

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Conditional aggregation and string aggregation →

Next steps

  1. 2 more topics in “Aggregation: GROUP BY and HAVING” before its exam: Conditional aggregation and string aggregation, Writing reports with aggregation.
  2. Module 4 exam: Aggregation, GROUP BY and HAVING · 15 questions · 25 min, without help. 5 topics of this module still to finish first.
  3. Then the next module, JOINs. 62 topics left in the course before the final exam.

← Previous: GROUP BY