CODEWITHSUNDEEP
sqldatabasegroup-by
BlackCat
BlackCat

Reputation: 2044

How can I get the count of total duplicate rows?

Suppose, I have a table "Employee"

|  id  | Name  |
----------------
|   1  |  AAA  |
----------------
|   1  |  AAA  |
----------------
|   2  |   BBB |
----------------
|   2  |  BBB  |
----------------
|   2  |  BBB  |
----------------

I want to count the duplicate rows so result will be 5 ( in total of 2 and 3)

The query I tried is:-

select count(id)
from Employee
group by id
having count(id) > 1

which give the output as expected:-

  | 2 |
   -----
   | 3 |
   -----

How can I achieve the total , I mean 5

Upvotes: 0

Views: 44

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28253

The simplest way would be to wrap it in another query:

SELECT SUM(counts) 
FROM (
select count(id) counts
from Employee
group by id
having count(id) > 1) inner_query

Edit:

a more complicated way would be to use group by, the sum window function & distinct in the same statement to avoid the inner sub query. 

SELECT DISTINCT SUM(COUNT(id)) OVER () count_duplicates
FROM Employee
GROUP BY id
HAVING COUNT(id) > 1

Upvotes: 1

Related Questions