Example 1
List email addresses that appear more than once
cara@example.com appears only once, so it is filtered out by the HAVING clause. Ada and Bob each appear twice, so they are the values flagged as duplicates.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE users (id INT, email VARCHAR(100));
INSERT INTO
users (id, email)
VALUES
(1, 'ada@example.com'),
(2, 'bob@example.com'),
(3, 'ada@example.com'),
(4, 'cara@example.com'),
(5, 'bob@example.com');Validated query Shared across supported engines.
SQL
SELECT
email,
COUNT(*) AS duplicate_count
FROM
users
GROUP BY
email
HAVING
COUNT(*) > 1
ORDER BY
email;Expected result Returned rows for the shared example.
| duplicate_count | |
|---|---|
| ada@example.com | 2 |
| bob@example.com | 2 |
All engines use the same `GROUP BY` plus `HAVING COUNT(*) > 1` duplicate check here.