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.

Share:
15,281
amateur
Author by

amateur

Updated on June 04, 2022

Comments

  • amateur
    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