CODEWITHSUNDEEP
mysql
Metropolis
Metropolis

Reputation: 6622

Find total duplicate entries on two columns

I need to get the TOTAL number of entries that contain duplicate data for multiple columns. It is exactly the same thing being done on this page where he has,

SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*)
FROM MyTable
GROUP BY COL_1, COL_2, COL_3, COL_4
HAVING COUNT(*) > 1

EXCEPT, I need sum(COUNT()). I have tried doing sum, and removing the group by clause, but I continue to get errors.

Upvotes: 2

Views: 721

Answers (2)

dazoakley
dazoakley

Reputation: 336

You can do this by making your grouped count a sub-query:

SELECT sum(sub.counts) FROM (
  SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*) as counts
  FROM MyTable
  GROUP BY COL_1, COL_2, COL_3, COL_4
  HAVING COUNT(*) > 1
) sub;

The most important thing really is to give the original 'COUNT(*)' a name so you can reference it easily in the outer query.

Upvotes: 2

Johan
Johan

Reputation: 76693

You can have both results by using with rollup 

SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*) as cnt
FROM MyTable
GROUP BY COL_1, COL_2, COL_3, COL_4 WITH ROLLUP
HAVING cnt > 1

The last row will be yield the grand total.

SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*) as cnt
FROM MyTable
GROUP BY COL_1, COL_2, COL_3, COL_4 WITH ROLLUP
HAVING cnt > 1
ORDER BY (COL_1 IS NOT NULL) ASC

This will yield the same result, but with the grand total as the first row.

Upvotes: 1

Related Questions