Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count of distinct rows with duplicate email IDs

Tags:

sql

How do I get count of distinct rows with duplicate email IDs in SQL?

ID   NAME   EMAIL 
1    John   [email protected] 
2    Sam    [email protected] 
4    Bob    [email protected] 
5    Tom    [email protected] 
6    Rob    [email protected]
7    Tic    [email protected]
8    Dad    [email protected]

The query should return 2. as [email protected] and [email protected] are duplicates

SELECT 
 COUNT(*)  
FROM Users
GROUP BY EMail 
HAVING ( COUNT(EMAIL) > 1 )    

This query is returning some wierd results. Thanks

like image 464
acadia Avatar asked Jul 21 '10 16:07

acadia


1 Answers

Count of rows:

select sum(cnt)
from (select count(*) as cnt
    from Users
    group by email
    having count(*) > 1) T

Count of emails:

select count(*)
from (select count(*) as cnt
    from Users
    group by email
    having count(*) > 1) T

Both at once:

select count(*) as COUNT_EMAIL sum(cnt) as COUNT_ROWS
from (select count(*) as cnt
    from Users
    group by email
    having count(*) > 1) T

Above should work on SQL Server and Oracle.

like image 193
Shannon Severance Avatar answered Oct 03 '22 01:10

Shannon Severance