具有重复电子邮件ID的不同行的计数

Question

如何获得SQL中具有重复电子邮件ID的不同行的计数？

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] 

查询应返回2. 为[email protected]和[email protected]是重复

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

该查询返回一些奇怪的结果。 感谢

Answer 1

计数行：

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

计数的电子邮件：

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

两者兼而有之：

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

以上应SQL Server和Oracle上工作。