2

I am writing a query to check if I have got duplicates in a certain column.

SELECT COUNT(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol 
  HAVING COUNT(*) > 1

That will give me an output like

3
5
6

Which of course is the number of records for each value of thecol where thecol is not unique.

Now how do I only get the sum 14? Should I have the existing query as a subquery?

  • 1
    Since you only count rows where thecol values are not null, COUNT(thecol) and COUNT(*) are inetrchangeable. No need to use both as it looks like they are counting something different. – ypercubeᵀᴹ Feb 5 at 20:27
  • I use this to diagnose duplicates in the database that should have been constrained from the start. – Anders Lindén Feb 8 at 22:58
4

Does this do what you want?

SELECT SUM(x.records)
FROM 
(
    SELECT COUNT(thecol) AS records
    FROM thetable 
    WHERE thecol IS NOT NULL 
    GROUP BY thecol 
    HAVING COUNT(*) > 1
) AS x
2

Another method which requires that the table has another column (with a PRIMARY KEY or UNIQUE NOT NULL constraint). Lets call it id:

SELECT COUNT(*)
FROM thetable AS t
WHERE EXISTS
      ( SELECT 1
        FROM thetable AS t2
        WHERE t2.thecol = t.thecol
          AND t2.id <> t.id
      ) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.