count distinct columns

[Mina Patel]

Hi trying to find the number of duplicate tuples/records in table based on multiple columns ie.

select count(distinct list multiple column key )
from x
where date = y
HAVING ( COUNT(multiple column key) > 1 )

doesn't seem to work....any tips? thanks

[miller]

Hi trying to find the number of duplicate tuples/records in table based on multiple columns ie.

select count(distinct list multiple column key )
from x
where date = y
HAVING ( COUNT(multiple column key) > 1 )

doesn't seem to work....any tips? thanks

Yes, one easy flaw in that statement is that you should make an alias for the COUNT, as you cannot employ the use of COUNT's in a HAVING clause. Additionally you should look into using a GROUP BY. I leave it up to you to look up the syntax for that keyword..

select count(distinct list multiple column key ) AS myCount
from x 
where date = y 
HAVING myCount > 1

[Mina Patel]

thanks but still doesn't work I have been trying the following:

select count(distinct id, product_id ) AS myCount
from prd_bal
where date = '30-jun-2006'
HAVING myCount > 1

[miller]

The below query will return all (id, product_id) combinations that match more than 1 record. This may not be the best method for your problem, but it's one that I know will work based on what you've told us so far.

SELECT id, product_id, COUNT(*) AS myCount
FROM prd_bal
WHERE date = '30-jun-2006'
GROUP BY id, product_id
HAVING myCount>1;

Note:
"id" is traditionally used as the primary key for most tables. If this is the case for "prd_bal", and it is unique (I would hope), then the above query will return no results. This should get you where you want to go though.

[Shanthi Sivaram]

select count(distinct column1 || column2 || column3) cnt from <tablename> where blah blah...