This query looks reasonable and fails:
SELECT city, SUM(amount) AS revenue
FROM orders
WHERE SUM(amount) > 5000
GROUP BY city;
ERROR 1111 (HY000): Invalid use of group function
The error is the whole difference between the two clauses in one line. WHERE filters individual rows before they are grouped, so at that moment there is no SUM() to test. HAVING filters groups after GROUP BY has built them, so it can test aggregates. Move the condition to HAVING and the query works.
Sample data
CREATE TABLE orders (
order_id INT PRIMARY KEY,
city VARCHAR(15),
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1,'Kanpur','delivered',2400), (2,'Kanpur','delivered',3100), (3,'Kanpur','returned',1800),
(4,'Agra','delivered',1500), (5,'Agra','delivered',900), (9,'Agra','returned',700),
(6,'Varanasi','delivered',4200),(7,'Varanasi','delivered',2600),(8,'Varanasi','returned',5000);
HAVING: filtering groups
SELECT city, SUM(amount) AS revenue
FROM orders
GROUP BY city
HAVING SUM(amount) > 5000
ORDER BY city;
| city | revenue |
|---|---|
| Kanpur | 7300.00 |
| Varanasi | 11800.00 |
Agra's total is 3,100, so its group is removed.
Using both: a question that needs each clause
"Which cities have more than ₹5,000 of delivered sales?" Returned orders must be excluded row by row (a WHERE job), and then the city totals tested (a HAVING job). First, what WHERE alone produces:
SELECT city, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
ORDER BY city;
| city | revenue |
|---|---|
| Agra | 2400.00 |
| Kanpur | 5500.00 |
| Varanasi | 6800.00 |
Now add the group filter:
SELECT city, SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY city
HAVING revenue > 5000
ORDER BY city;
| city | revenue |
|---|---|
| Kanpur | 5500.00 |
| Varanasi | 6800.00 |
Notice how Varanasi's total dropped from 11,800 to 6,800: the ₹5,000 return was removed by WHERE before summing. If you had tried to remove returns in HAVING, it would be too late; the returned amount is already inside the sum.
The order MySQL works in
| Step | Clause | Works on |
|---|---|---|
| 1 | FROM / JOIN | Builds the rows |
| 2 | WHERE | Keeps or drops each row |
| 3 | GROUP BY | Collapses rows into groups |
| 4 | HAVING | Keeps or drops each group |
| 5 | SELECT | Computes output columns and aliases |
| 6 | ORDER BY, LIMIT | Sorts and trims the result |
This is the logical order (the optimizer may do things differently internally, but the result must be as if it followed these steps). It explains the rules below.
Side-by-side summary
| WHERE | HAVING | |
|---|---|---|
| Filters | Rows | Groups (or the single group of the whole table) |
| Runs | Before GROUP BY | After GROUP BY |
Aggregates such as SUM(), COUNT() | Not allowed (error 1111) | Allowed |
| Select-list aliases | Not allowed (error 1054) | Allowed in MySQL |
| Can use indexes to avoid reading rows | Yes | No, groups are built first |
| Works in UPDATE / DELETE | Yes | No |
MySQL-specific behaviour
- Aliases.
HAVING revenue > 5000works in MySQL, as shown above. InWHEREit does not:SELECT order_id, amount * 1.18 AS with_gst FROM orders WHERE with_gst > 5000;gives ERROR 1054: Unknown column 'with_gst' in 'where clause'. Repeat the expression inWHEREinstead. Note that some databases do not accept aliases inHAVING, soHAVING SUM(amount) > 5000is the portable form. - HAVING without GROUP BY.
SELECT SUM(amount) AS total FROM orders HAVING total > 20000;treats the whole table as one group and returns22200.00; with a threshold of 30000 it would return an empty result.
The performance mistake: filtering rows in HAVING
MySQL accepts a non-aggregate condition in HAVING when the column is in GROUP BY, and these two queries return the same result:
SELECT city, SUM(amount) FROM orders GROUP BY city HAVING city <> 'Agra'; -- slower pattern
SELECT city, SUM(amount) FROM orders WHERE city <> 'Agra' GROUP BY city; -- better
| city | SUM(amount) |
|---|---|
| Kanpur | 7300.00 |
| Varanasi | 11800.00 |
The first version reads and groups Agra's rows only to throw the group away; the second never groups them, and can use an index on city. On nine rows nobody notices; on a crore rows it matters. Rule: if a condition can be checked on a single row, put it in WHERE.
A typical interview question
"Show cities with at least two orders above ₹1,000." Row condition (amount > 1000) in WHERE, group condition (COUNT(*) >= 2) in HAVING:
SELECT city, COUNT(*) AS orders
FROM orders
WHERE amount > 1000
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY city;
| city | orders |
|---|---|
| Kanpur | 3 |
| Varanasi | 3 |
Agra has only one order above ₹1,000 and drops out. More grouping practice is in the GROUP BY clause explained and aggregate functions COUNT, SUM and AVG; row filtering in depth is in the WHERE clause with examples.