I am trying to write query to show count of duplicate rows in a table based on a set of columns. I see the numbers I need by using the following:
SELECT SUM(COUNT(*)) CNT
FROM MYTABLE
GROUP BY COL1, COL2,COL3,COL4
HAVING COUNT(*)>1;
Will show my total records if I subtract the number of rows returned from this query:
SELECT COUNT(1) CNT
FROM MYTABLE
GROUP BY COL1, COL2,COL3,COL4
HAVING COUNT(*)>1;
How can I subtract the number of rows in the second query from the value in the first query or is there a better way to get this.
I am trying to show a count of all the duplicates in may table based on a combo of columns.
I hope this is explained right.
Thank you,
David Miller