SQL: Count duplicate records based on condition

Question

The below is a small section of a query that I need help with. This section produces a count of records where both the EmailAddress and DateOfBirth are duplicated.

The commented out line should produce a count of records where the EmailAddress is duplicated but the DateOfBirth differs. i.e. Identify users who share an email address (assuming two users would have different date of births).

SELECT     
u.EmailAddress,
u.DateOfBirth,
COUNT(*) over (partition by u.EmailAddress, DateOfBirth) AS EmailAndDoBDup,

--COUNT(*) where EmailAddress is duplicate but DateOfBirth is unique (in the aggregated results)

FROM [User] AS u

Thanks

it's better to mention or tag the `RDBMS` also, it's MySQL, MS SQL Server, or etc? — void, Jan 23 '15 at 16:55
use a combination of SUM and CASE: SUM(CASE WHEN SomeCondition=True THEN 1 ELSE 0 END) — Tab Alleman, Jan 23 '15 at 17:00
possible duplicate of [SQL Equivalent of COUNTIF()](http://stackoverflow.com/questions/582637/sql-equivalent-of-countif) — Tab Alleman, Jan 23 '15 at 17:02

score 0 · Answer 1 · answered Jan 23 '15 at 17:06

You can do this with a subquery. I don't think there is a way to do this with a single window function:

SELECT u.EmailAddress, u.DateOfBirth,
       EmailAndDoBDup,
       SUM(CASE WHEN EmailAndDoBDup = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY EmailAddress) as YourCol
FROM (SELECT u.*,
             COUNT(*) OVER (partition by u.EmailAddress, DateOfBirth) as EmailAndDoBDup
      FROM [User] u
     ) u;

EDIT:

If you wanted one row per email address and DOB, you can phrase this as an aggregation query:

SELECT u.EmailAddress, u.DateOfBirth, COUNT(*) as EmailAndDoBDup,
       SUM(CASE WHEN COUNT(*) = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY EmailAddress) as YourCol
FROM [User] u
GROUP BY u.EmailAddress, u.DateOfBirth;

This doesn't require the subquery, but it might not fit into your more complex query.

score 0 · Answer 2 · answered Jan 23 '15 at 18:04

instead of doing it in the SELECT section, I would left outer join the two sets, like:

LEFT OUTER join
(SELECT     
    EmailAddress,
    DateOfBirth
FROM
    USER
GROUP BY
    EmailAddress,
    DateOfBirth
HAVING
    COUNT(DISTINCT ID) > 1) dupEmailDOB
...
LEFT OUTER JOIN
(SELECT     
    EmailAddress
FROM
    USER
GROUP BY
    EmailAddress
HAVING
    COUNT(DISTINCT DateOfBirth) > 1) emailMultipleDOBs

because it's easier to maintain if you need to add additional criteria

SQL: Count duplicate records based on condition

2 Answers2