How to apply Count on multiple distinct columns and use Having clause
15,281
Solution 1
If you group by these columns then you already only get those unique records and then you can use count(*)
to get how many duplicates you have
select A,B,C, count(*)
from table_name
group by A,B,C
HAVING count(*) > 1
Solution 2
What @jurgend said is right, and you can further find the exact rows (I'm assuming there are more fields to look at, including maybe a PK) by doing
SELECT *
FROM table_name
WHERE (A,B,C) IN (
SELECT A, B, C
FROM table_name
GROUP BY A, B, C
HAVING COUNT(*) > 1
)
A Tuple IN
list query works in Oracle, although not all other DBMS.
Author by
amateur
Updated on June 04, 2022Comments
-
amateur almost 2 years
I would like to do something like this , but getting an error please suggest some good methods?
select A,B,C, count(Distinct A,B,C) from table_name group by A,B,C having count(Distinct A,B,C) > 1
Basically i have an index on the columns(A,B,C), and some rows doesnt have this unique combination set, So I'm trying a query similar to identify the rows which disobeys the unique constraint. PLease let me know if there is a best way