This one has been driving me crazy for years.

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 (COUNT, MAX, etc) with a WHERE. The solution is to use HAVING, which is applied after the GROUP BY:

SELECT COUNT(*) AS count, * FROM users GROUP BY email HAVING count > 1;

This will give the expected result.