HAVING versus WHERE
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)WHERE decides which rows go into the groups; HAVING decides which groups come out. "Successful payments only" is a row filter, and "payers who spent more than Rs 500" is a group filter, so a real report often needs both.
WHERE filters rows before grouping
WHERE checks each row on its own and removes the rows that fail before any group is formed. Because no group exists yet, WHERE cannot use an aggregate such as COUNT(*).
The first example counts employees hired before 2020, per department, with WHERE filtering before grouping. The second asks WHERE for a count and gets an error.
Example 1 · WHERE first: old hands in each department
-- Employees hired before 2020, per departmentSELECT dept_id, COUNT(*) AS people, SUM(salary) AS salary_billFROM employeesWHERE hire_date < '2020-01-01'GROUP BY dept_idORDER BY dept_id;+---------+--------+-------------+ | dept_id | people | salary_bill | +---------+--------+-------------+ | 1 | 3 | 550000.00 | | 2 | 1 | 150000.00 | | 3 | 1 | 90000.00 | | 4 | 1 | 140000.00 | +---------+--------+-------------+
What this code does
WHEREremoves everyone hired in 2020 or later before grouping, so Engineering's count drops from 5 to 3.- Departments whose rows were all removed, such as the
NULLgroup (Tanvi joined in 2025), do not appear at all: no rows, no group.
Example 2 · Error 1111: an aggregate in WHERE
-- Goal: cities with at least 2 customersSELECT city, COUNT(*) AS customersFROM customersWHERE COUNT(*) >= 2GROUP BY city;ERROR 1111 (HY000): Invalid use of group function
What this code does
WHEREis checked on single rows before the groups exist, soCOUNT(*)has nothing to count there. MySQL stops with error 1111.- The fix is to move the condition after
GROUP BYintoHAVING, which the next example does.
Use WHERE for anything you can decide by looking at one row: a status, a date range, a city, a price limit.
Find the bug
Find the misplaced test
Select the line that is wrong.
This should list cities with at least 2 customers, but fails with error 1111. Which line is wrong?
Round 1 of 2HAVING filters groups after grouping
HAVING checks each group after GROUP BY has built it and keeps only the groups whose condition is true. It is the place for conditions on aggregates, such as HAVING COUNT(*) >= 2.
Which cities have at least two customers? MySQL builds every city group first, then HAVING drops the small ones, so only the groups you asked for come back.
Example 3 · HAVING keeps only the groups you want
SELECT city, COUNT(*) AS customersFROM customersGROUP BY cityHAVING COUNT(*) >= 2ORDER BY city;
SELECT category_id, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_priceFROM productsGROUP BY category_idHAVING AVG(price) > 10000ORDER BY avg_price DESC;+---------+-----------+ | city | customers | +---------+-----------+ | Chennai | 2 | | Delhi | 2 | | Pune | 2 | +---------+-----------+ +-------------+----------+-----------+ | category_id | products | avg_price | +-------------+----------+-----------+ | 3 | 2 | 53990.00 | | 2 | 3 | 35966.00 | +-------------+----------+-----------+
What this code does
- All 7 city groups are built first;
HAVING COUNT(*) >= 2then keeps only the 3 cities with two customers. - The second query keeps the categories whose average price is above Rs 10,000: Laptops (3) and Mobiles (2). The other groups are dropped after their averages are known.
HAVING can use aggregates and the grouping columns. A plain column that is not grouped is not available there: HAVING status = 'PLACED' in a query grouped by customer_id fails with ERROR 1054 (42S22): Unknown column 'status' in 'having clause'.
Predict the output
How many groups come back?
Read the code and pick what it prints.
Round 1 of 2
Customers live in 7 cities; some cities have 2 customers.
Using both in one query
WHERE and HAVING can work in one query: WHERE removes unwanted rows first, GROUP BY makes the groups, and HAVING then removes unwanted groups. A condition in the wrong clause changes the answer.
Which payers spent more than Rs 500 in successful payments? The example runs a wrong query and the right one: WHERE drops the failed rows, GROUP BY payer makes groups, and HAVING keeps the big spenders.
Example 4 · UPI big spenders: WHERE and HAVING together
DROP TABLE IF EXISTS upi_payments;CREATE TABLE upi_payments ( txn_id INT PRIMARY KEY, payer VARCHAR(20) NOT NULL, merchant VARCHAR(30) NOT NULL, amount DECIMAL(8,2) NOT NULL, status VARCHAR(10) NOT NULL);
INSERT INTO upi_payments VALUES (1, 'Ananya', 'Campus Gadgets', 120.00, 'SUCCESS'), (2, 'Ananya', 'Xerox Corner', 45.00, 'SUCCESS'), (3, 'Ananya', 'Hostel Fees', 2500.00, 'FAILED'), (4, 'Ananya', 'Hostel Fees', 2500.00, 'SUCCESS'), (5, 'Bharat', 'Campus Gadgets', 80.00, 'SUCCESS'), (6, 'Bharat', 'Metro Card', 500.00, 'FAILED'), (7, 'Bharat', 'Metro Card', 500.00, 'FAILED'), (8, 'Bharat', 'Campus Gadgets', 150.00, 'SUCCESS'), (9, 'Chitra', 'Xerox Corner', 30.00, 'SUCCESS'), (10, 'Chitra', 'Hostel Fees', 2500.00, 'SUCCESS'), (11, 'Chitra', 'Campus Gadgets', 200.00, 'SUCCESS'), (12, 'Dev', 'Metro Card', 300.00, 'SUCCESS'), (13, 'Dev', 'Campus Gadgets', 90.00, 'FAILED'), (14, 'Dev', 'Xerox Corner', 60.00, 'SUCCESS');
-- Wrong: failed payments are counted as spendingSELECT payer, SUM(amount) AS spentFROM upi_paymentsGROUP BY payerHAVING SUM(amount) > 500ORDER BY payer;
-- Right: keep successful rows, then keep big groupsSELECT payer, COUNT(*) AS payments, SUM(amount) AS spentFROM upi_paymentsWHERE status = 'SUCCESS'GROUP BY payerHAVING SUM(amount) > 500ORDER BY spent DESC;+--------+---------+ | payer | spent | +--------+---------+ | Ananya | 5165.00 | | Bharat | 1230.00 | | Chitra | 2730.00 | +--------+---------+ +--------+----------+---------+ | payer | payments | spent | +--------+----------+---------+ | Chitra | 3 | 2730.00 | | Ananya | 3 | 2665.00 | +--------+----------+---------+
What this code does
- The wrong query lists Bharat with Rs 1,230, but Rs 1,000 of that is two failed metro card payments. Ananya's failed Rs 2,500 hostel fee payment also doubles her total.
- The right query drops failed rows in
WHEREfirst. Only Chitra and Ananya really spent more than Rs 500; Bharat (Rs 230) and Dev (Rs 360) disappear inHAVING.
If failed payments are not removed by WHERE, they are added into the totals, and a payer whose payments mostly failed looks like a big spender. A condition on a grouping column, such as payer <> 'Dev', works in either clause; put it in WHERE, so fewer rows are grouped.
Order of execution
Put the report in order
Arrange the clauses of the big-spenders query.
WHERE. "Does it need a total, count or average?" → HAVING.COUNT(DISTINCT ...) and conditional counts with SUM(CASE ...)
COUNT(DISTINCT column) counts the different non-NULL values in a column, not the rows. A conditional count, SUM(CASE WHEN ... THEN 1 ELSE 0 END), counts only the matching rows inside each group.
Fifteen orders come from nine customers, so COUNT(*) is 15 but COUNT(DISTINCT customer_id) is 9. The query below counts rows, different customers and different statuses.
SELECT COUNT(*) AS order_rows, COUNT(DISTINCT customer_id) AS customers, COUNT(DISTINCT status) AS statusesFROM orders;+------------+-----------+----------+ | order_rows | customers | statuses | +------------+-----------+----------+ | 15 | 9 | 4 | +------------+-----------+----------+
A conditional count counts only the rows that match a condition: SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END). The CASE turns each row into 1 or 0 and SUM adds them, so one query can give delivered, shipped and cancelled counts side by side. MySQL also accepts SUM(status = 'DELIVERED'); the CASE form works in every SQL database.
Example 5 · Orders per month: distinct customers and status counts
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS orders, COUNT(DISTINCT customer_id) AS customers, SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END) AS delivered, SUM(status = 'CANCELLED') AS cancelledFROM ordersGROUP BY monthORDER BY month;+---------+--------+-----------+-----------+-----------+ | month | orders | customers | delivered | cancelled | +---------+--------+-----------+-----------+-----------+ | 2025-01 | 2 | 2 | 2 | 0 | | 2025-02 | 3 | 3 | 2 | 1 | | 2025-03 | 3 | 3 | 2 | 0 | | 2025-04 | 3 | 3 | 1 | 0 | | 2025-05 | 3 | 3 | 1 | 1 | | 2025-06 | 1 | 1 | 0 | 0 | +---------+--------+-----------+-----------+-----------+
What this code does
COUNT(DISTINCT customer_id)counts each customer once per month, so it can be smaller thanorderswhen someone orders twice in a month.- The
CASEgives 1 for a delivered order and 0 otherwise;SUMadds them per month.SUM(status = 'CANCELLED')does the same with MySQL's shorter form.
Example 6 · Exam results: pass, fail and pending per course
SELECT course_id, COUNT(*) AS enrolled, SUM(CASE WHEN marks >= 40 THEN 1 ELSE 0 END) AS passed, SUM(CASE WHEN marks < 40 THEN 1 ELSE 0 END) AS failed, SUM(CASE WHEN marks IS NULL THEN 1 ELSE 0 END) AS pendingFROM enrollmentsGROUP BY course_idHAVING SUM(CASE WHEN marks >= 40 THEN 1 ELSE 0 END) >= 2ORDER BY course_id;+-----------+----------+--------+--------+---------+ | course_id | enrolled | passed | failed | pending | +-----------+----------+--------+--------+---------+ | 1 | 4 | 4 | 0 | 0 | | 2 | 4 | 3 | 1 | 0 | | 3 | 2 | 2 | 0 | 0 | | 4 | 4 | 4 | 0 | 0 | +-----------+----------+--------+--------+---------+
What this code does
- For the
NULLmark,marks >= 40andmarks < 40are both unknown, soCASEfalls toELSE 0. Thependingcolumn counts it instead. HAVINGrepeats thepassedexpression to keep only courses where at least 2 students passed. Course 5 has one pass and one pending, so it is dropped.
Next step
Check your understanding
12 quick questions