This one has been driving me crazy for years helpful site.
Let’s say that you have a MySQL database with a bunch of users in it, and you want to see only those users that have duplicate email addresses. You’d think this would work, but it doesn’t:
SELECT COUNT(*) AS count, * FROM users WHERE count > 1 GROUP BY email;
The problem is that
WHERE is applied before the
GROUP BY, so you can’t use aggregation functions (
MAX, etc) with a
WHERE. The solution is to use
HAVING, which is applied after the
SELECT COUNT(*) AS count, * FROM users GROUP BY email HAVING count > 1;
This will give the expected result.