Which query finds duplicate emails in a users table?

Options

1. SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1 (Correct option)
2. SELECT DISTINCT email, COUNT(*) FROM users ORDER BY email DESC
3. SELECT email FROM users WHERE COUNT(email) > 1 GROUP BY email
4. SELECT email FROM users GROUP BY id HAVING COUNT(email) > 1

Answer

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

Why

Group rows by email, count how many rows are in each group, and keep only groups with more than one row. HAVING is used because the filter applies to the aggregated result (WHERE cannot use COUNT).

To prevent duplicates in the future, add a UNIQUE constraint on email.

Remember

Duplicates: GROUP BY col HAVING COUNT(*) > 1.

Key terms

• GROUP BY
• Aggregate function
• COUNT(*) vs COUNT(column)
• SELECT / WHERE / HAVING / ORDER BY
• UNIQUE constraint