Number of individual lines with duplicate email identifiers

Question

How to get the number of rows with duplicate email ids in SQL?

ID NAME EMAIL 1 John [e-mail] 2 Sam [e-mail] 4 Bob [e-mail] 5 Tom [e-mail] 6 Rob [e-mail] 7 Tic [e-mail] 8 Dad [e-mail] 

The request should return 2. [e-mail] and [e-mail] are duplicates

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

This query returns some wierd results. Thanks

Answer 1

Number of lines:

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

Number of letters:

 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.