zahed zahed - 12 days ago 7
SQL Question

How to get sum of duplication count value?

I have following below table:

CandidateId CandidateName
can132 Mazhar
can132 Mazhar
can133 Raj
can133 Raj
can134 Rahul
can134 Rahul
can134 Rahul


My SQL query base on candidate id getting duplication.

SELECT CandidateId, COUNT(*) as duplicate
FROM Table_CandidateInfo_Preview
GROUP BY CandidateId
HAVING COUNT(*) > 1
ORDER BY CandidateId


Right I am getting below output:

CandidateId duplicate
can132 2
can133 2
can134 3


I need result like final value
4
to send my output parameter in my stored procedure because three Candidate Id have duplication and
cand132
1 time,
cand133
1 time and
can134
2 time. Sum of all will get
4
value.

Answer

Wrap your query up in a derived table (slightly adjusted). SUM its duplicate values:

select sum(duplicate)
from
(
    SELECT CandidateId, COUNT(*) - 1 as duplicate
    FROM Table_CandidateInfo_Preview
    GROUP BY CandidateId
    HAVING COUNT(*) > 1
) dt
Comments