Count of distinct rows with duplicate email IDs

2018-06-10 07:09:29

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

ID   NAME   EMAIL 
1    John   asd@asd.com 
2    Sam    asd@asd.com 
4    Bob    bob@asd.com 
5    Tom    asd@asd.com 
6    Rob    bob@asd.com
7    Tic    tic@asd.com
8    Dad    dad@asd.com

The query should return 2. as asd@asd.com and bob@asd.com are duplicates

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

This query is returning some wierd results. Thanks

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.

链接地址: http://www.djcxy.com/p/30510.html

上一篇: 一个表，两列MYSQL查询挑战

下一篇: 统计具有重复电子邮件ID的不同行