WHERE filters individual rows before GROUP BY, while HAVING filters the groups after it and can use aggregates like SUM() and COUNT(). Here is how they differ, with real results, errors and query plans from MySQL 8.4, PostgreSQL 17 and SQLite 3.46.
WHERE filters individual rows before they are grouped, and HAVING filters whole groups after GROUP BY has run. That's why only HAVING can use aggregate functions such as SUM(), COUNT() and AVG(). Use WHERE for conditions on plain columns, like status = 'paid', and HAVING for conditions on totals, like SUM(amount) > 100. You can use both in one query, and WHERE always runs first.
The PostgreSQL SELECT reference puts it in one line: WHERE filters individual rows before GROUP BY is applied, while HAVING filters group rows created by GROUP BY. The rest of this guide shows what that means in practice, including the errors you get when you mix them up and a case where the wrong choice makes MySQL read the whole table.
I ran every query below on MySQL 8.4.8 (default ONLY_FULL_GROUP_BY mode), PostgreSQL 17.11 and SQLite 3.46.1. All the results and error messages are copied from those runs.
In this guide:
| WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Groups of rows |
| Runs | Before GROUP BY | After GROUP BY and aggregation |
Aggregates like SUM() | Not allowed (error) | Allowed, and the main reason to use it |
| Needs GROUP BY? | No | Usually used with it; without it the whole result is one group |
| Works in UPDATE and DELETE | Yes | No, SELECT only |
| Can use indexes | Yes | Not reliably (see the performance test below) |
| SELECT aliases | Not allowed in MySQL or PostgreSQL | Allowed in MySQL and SQLite, not in PostgreSQL |
If you remember one rule, make it this: if the condition mentions an aggregate, it goes in HAVING. Otherwise, it goes in WHERE.
Every example uses the same ten orders from four customers. It's small enough to check the answers by hand:
CREATE TABLE orders (
id INT PRIMARY KEY,
customer VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL,
status VARCHAR(10) NOT NULL,
amount DECIMAL(8,2) NOT NULL
);
INSERT INTO orders VALUES
(1, 'Ana', 'London', 'paid', 60.00),
(2, 'Ana', 'London', 'paid', 55.00),
(3, 'Ana', 'London', 'refunded', 40.00),
(4, 'Ben', 'Leeds', 'paid', 120.00),
(5, 'Ben', 'Leeds', 'pending', 30.00),
(6, 'Cara', 'London', 'pending', 25.00),
(7, 'Cara', 'London', 'pending', 15.00),
(8, 'Dev', 'Leeds', 'paid', 20.00),
(9, 'Dev', 'Leeds', 'paid', 35.00),
(10,'Dev', 'Leeds', 'paid', 10.00);I show MySQL's output in most places. PostgreSQL and SQLite returned the same rows unless I say otherwise. SQLite prints 155 where the others print 155.00, because it doesn't keep DECIMAL scale. If joins are still fuzzy for you, my guide to INNER vs LEFT vs RIGHT vs FULL OUTER JOIN uses a similar shop table.
You write SQL in one order, but the database evaluates it in another. This logical order explains almost every WHERE vs HAVING question:
SUM() and COUNT() are calculated per group.Because WHERE runs before step 4, no totals exist yet when it runs, so it can't test them. HAVING runs after step 4, so it can. The PostgreSQL tutorial on aggregate functions walks through the same idea. Real query planners can reorder work for speed, but they must return the same result as this logical order.
WHERE checks each row on its own. Here it keeps paid orders worth at least 50:
SELECT id, customer, status, amount
FROM orders
WHERE status = 'paid' AND amount >= 50
ORDER BY id;+----+----------+--------+--------+
| id | customer | status | amount |
+----+----------+--------+--------+
| 1 | Ana | paid | 60.00 |
| 2 | Ana | paid | 55.00 |
| 4 | Ben | paid | 120.00 |
+----+----------+--------+--------+No grouping is involved. Each of the ten rows passes or fails by itself, and three pass. WHERE is also the clause you use in UPDATE and DELETE, where HAVING isn't allowed at all.
Now say you want customers who have spent more than 100 in total. That condition is about a group of rows, so it belongs in HAVING:
SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100
ORDER BY customer;+----------+--------+--------+
| customer | orders | total |
+----------+--------+--------+
| Ana | 3 | 155.00 |
| Ben | 2 | 150.00 |
+----------+--------+--------+GROUP BY made four groups (Ana, Ben, Cara and Dev). HAVING kept the two whose total is over 100. Cara (40.00) and Dev (65.00) were removed as whole groups.
HAVING can combine several aggregate conditions, and they don't have to appear in the SELECT list:
SELECT customer, COUNT(DISTINCT status) AS statuses, MAX(amount) AS biggest
FROM orders
GROUP BY customer
HAVING COUNT(DISTINCT status) > 1 AND MAX(amount) >= 50
ORDER BY customer;| Ana | 2 | 60.00 |
| Ben | 2 | 120.00 |The most common real use of HAVING COUNT(*) > 1 is finding duplicates. I use it that way in how to delete duplicate rows in MySQL but keep one.
Put an aggregate in WHERE and all three databases refuse to run the query. This is the exact query:
SELECT customer, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 100
GROUP BY customer;And these are the three errors:
ERROR 1111 (HY000) at line 1: Invalid use of group functionERROR: aggregate functions are not allowed in WHERE
LINE 3: WHERE SUM(amount) > 100Parse error near line 1: misuse of aggregate: SUM()That's MySQL, PostgreSQL and SQLite in order. MySQL's "Invalid use of group function" is the least obvious of the three, but it means the same thing. Fix: move the aggregate condition into HAVING, as in the previous section. The MySQL SELECT documentation says plainly that WHERE can use any function except aggregate (group) functions.
The two clauses work well as a team. WHERE trims the rows first, then HAVING judges the groups that are left. This query asks: which customers have at least two paid orders?
SELECT customer, COUNT(*) AS paid_orders, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING COUNT(*) >= 2
ORDER BY customer;+----------+-------------+------------+
| customer | paid_orders | paid_total |
+----------+-------------+------------+
| Ana | 2 | 115.00 |
| Dev | 3 | 65.00 |
+----------+-------------+------------+Compare that with the same HAVING but no WHERE. Now every customer qualifies, because everyone has at least two orders of some kind:
| Ana | 3 | 155.00 |
| Ben | 2 | 150.00 |
| Cara | 2 | 40.00 |
| Dev | 3 | 65.00 |Ben drops out of the first query because WHERE removed his pending order before counting, which left him one paid order. Ana's total falls from 155.00 to 115.00 because her refunded order was removed. Where you put a condition changes the numbers, not just which rows appear.
Here's a mistake I see in dashboards all the time. You want the number of paid orders for every customer, so you filter with WHERE:
SELECT customer, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer
ORDER BY customer;| Ana | 2 |
| Ben | 1 |
| Dev | 3 |Cara has vanished. She has no paid orders, so WHERE removed all her rows and there was nothing left to group. You wanted to see "Cara: 0". Fix: keep all rows and count conditionally inside the aggregate:
SELECT customer,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
COUNT(*) AS all_orders
FROM orders
GROUP BY customer
ORDER BY customer;| Ana | 2 | 3 |
| Ben | 1 | 2 |
| Cara | 0 | 2 |
| Dev | 3 | 3 |PostgreSQL and SQLite also support the standard FILTER clause, COUNT(*) FILTER (WHERE status = 'paid'), which gave the same four rows. MySQL 8.4.8 doesn't support it and returned ERROR 1064 (42000), a syntax error, so use the CASE version if your code has to run on MySQL. SQLite documents FILTER on its aggregate functions page.
You can put a plain column condition in HAVING, as long as that column is in the GROUP BY. These two queries returned the identical result in all three databases:
SELECT city, COUNT(*) AS orders
FROM orders
GROUP BY city
HAVING city = 'London';SELECT city, COUNT(*) AS orders
FROM orders
WHERE city = 'London'
GROUP BY city;| London | 5 |So does it matter which one you use? To find out, I loaded a 100,000-row table with 1,000 customers and an index on customer_id, then compared the query plans for WHERE customer_id = 42 and HAVING customer_id = 42. Both queries returned a total of 9400. This is MySQL's plan for the WHERE version:
-> Group aggregate: sum(big_orders.amount) (cost=45 rows=1)
-> Index lookup on big_orders using idx_big_customer (customer_id=42) (cost=35 rows=100)And this is the plan for the HAVING version:
-> Filter: (big_orders.customer_id = 42) (cost=20076 rows=982)
-> Group aggregate: sum(big_orders.amount) (cost=20076 rows=982)
-> Index scan on big_orders using idx_big_customer (cost=10066 rows=100098)With WHERE, MySQL looked up about 100 rows through the index. With HAVING, it scanned the whole index (about 100,000 rows), built every customer's total, then threw away all but one. The estimated cost went from 45 to 20076. The MySQL manual warns about this directly: HAVING is applied nearly last, "with no optimization", and you shouldn't use it for items that belong in WHERE.
PostgreSQL 17 and SQLite were smarter. Both moved the condition down and used the index for either version:
-> Bitmap Index Scan on idx_big_customer
Index Cond: (customer_id = 42)`--SEARCH big_orders USING INDEX idx_big_customer (customer_id=?)You can't count on that, though, so the safe habit is the same everywhere: plain column conditions go in WHERE. You can check your own queries with EXPLAIN.
What happens if HAVING uses a column that is neither grouped nor aggregated? Here I group by customer but filter on status:
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING status = 'paid';ERROR 1054 (42S22) at line 1: Unknown column 'status' in 'having clause'ERROR: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate functionMySQL and PostgreSQL both reject it, which is the right call, because a group like Ana's contains both paid and refunded orders. SQLite, however, ran it and returned three rows:
| Ana | 155 |
| Ben | 150 |
| Dev | 65 |That's a silent wrong answer. Ana's total of 155 includes a refunded order, and Ben's includes a pending one. The SQLite SELECT docs explain why: a non-aggregate HAVING expression is evaluated against an arbitrarily selected row from each group. If you meant "only paid orders", that condition belongs in WHERE.
Aliases are where the three databases disagree most. I tested a SELECT alias in HAVING (HAVING total > 100, where total is SUM(amount)) and in WHERE (WHERE with_vat > 100, where with_vat is amount * 1.2):
| Database | Alias in HAVING | Alias in WHERE |
|---|---|---|
| MySQL 8.4.8 | Works | Error 1054: Unknown column 'with_vat' in 'where clause' |
| PostgreSQL 17.11 | Error: column "total" does not exist | Error: column "with_vat" does not exist |
| SQLite 3.46.1 | Works | Works |
ERROR: column "total" does not exist
LINE 4: HAVING total > 100That's PostgreSQL's documented behaviour: output column names work in ORDER BY and GROUP BY, but not in WHERE or HAVING. The portable fix is to repeat the expression (HAVING SUM(amount) > 100), or to compute it in a CTE and filter it with WHERE outside:
WITH totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
SELECT customer, total
FROM totals
WHERE total > 100
ORDER BY customer;| Ana | 155.00 |
| Ben | 150.00 |That CTE ran unchanged on all three databases. When you build these filters from user input in PHP, pass the values as placeholders, as shown in my PDO prepared statements guide, whether the condition sits in WHERE or HAVING.
HAVING without GROUP BY treats the whole filtered result as one group. With an aggregate, all three databases agree:
SELECT COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
HAVING COUNT(*) > 3;| 6 |The PostgreSQL docs note that a query like this returns one row when the HAVING condition is true and zero rows when it isn't. Things split when there's no aggregate at all, as in SELECT id, amount FROM orders HAVING amount > 100. MySQL treats it like a WHERE and returns order 4. The other two refuse:
ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate functionParse error near line 1: HAVING clause on a non-aggregate queryThat's PostgreSQL first, then SQLite. Code that relies on MySQL's leniency here will break if you ever move databases.
status, city, a date)? Use WHERE.SUM(), COUNT(), AVG(), MIN() or MAX()? Use HAVING.CASE, or FILTER outside MySQL.EXPLAIN and check that your WHERE condition uses an index.I keep a set of small browser-based helpers for everyday developer jobs on my free tools page, if you need to tidy test data before importing it.
WHERE filters individual rows before they are grouped, and HAVING filters groups after GROUP BY and aggregation. That's why HAVING can test SUM(), COUNT() and other aggregates, while WHERE can't.
Yes. The whole filtered result then counts as one group, so a query like SELECT COUNT(*) FROM orders HAVING COUNT(*) > 3 returns one row or none. A HAVING with no aggregate at all works in MySQL but fails in PostgreSQL and SQLite.
WHERE runs before rows are grouped, so the totals don't exist yet. MySQL reports Invalid use of group function, PostgreSQL reports aggregate functions are not allowed in WHERE, and SQLite reports misuse of aggregate. Move the condition to HAVING.
It can be. In my MySQL 8.4.8 test, a plain column condition in HAVING scanned all 100,000 rows, while the same condition in WHERE read about 100 through an index. PostgreSQL 17 and SQLite optimised both forms the same way, but WHERE is the safe choice everywhere.
WHERE runs first. The logical order is FROM, WHERE, GROUP BY, aggregates, HAVING, SELECT, then ORDER BY and LIMIT.
In MySQL and SQLite, yes. In PostgreSQL, no: it reports that the column does not exist. Repeat the expression, for example HAVING SUM(amount) > 100, or wrap the query in a CTE if you need portable SQL.
Yes, and it's common. WHERE removes unwanted rows first, then HAVING filters the groups that are left. Keep in mind that the WHERE condition changes what gets counted and summed.
Last checked: 4 November 2026. Figures, settings and guidance can change, so confirm anything important with the sources linked above.
INNER JOIN keeps only matching rows, LEFT JOIN keeps every row from the left table, RIGHT JOIN keeps every row from the right table, and FULL OUTER JOIN keeps everything. Here's each one run on the same two tables in MySQL 8.4 and SQLite, with real output.
GROUP_CONCAT(col ORDER BY col SEPARATOR ', ') turns many rows into one comma-separated string per group. Tested examples for ORDER BY, SEPARATOR, DISTINCT, NULLs, the 1024-byte cut-off, top-N and JSON_ARRAYAGG on MySQL 8.4.
Find duplicates with GROUP BY and HAVING, preview them with ROW_NUMBER(), then delete all but one with a self-join. Error 1093, NULLs, case and spaces, and a UNIQUE key.
$stmt->fetch(PDO::FETCH_ASSOC) returns one row as an array, fetchAll() returns them all and fetchColumn() returns one value. Tested fetch modes, JOIN key clashes and the rowCount() trap.
0%