By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,905 Members | 1,643 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,905 IT Pros & Developers. It's quick & easy.

count distinct columns

P: 2
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
Oct 18 '06 #1
Share this Question
Share on Google+
4 Replies


miller
Expert 100+
P: 1,089
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..

Expand|Select|Wrap|Line Numbers
  1. select count(distinct list multiple column key ) AS myCount
  2. from x 
  3. where date = y 
  4. HAVING myCount > 1
Oct 18 '06 #2

P: 2
thanks but still doesn't work I have been trying the following:
Expand|Select|Wrap|Line Numbers
  1. select count(distinct id, product_id ) AS myCount
  2. from prd_bal
  3. where date = '30-jun-2006'
  4. HAVING myCount > 1
Oct 19 '06 #3

miller
Expert 100+
P: 1,089
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT id, product_id, COUNT(*) AS myCount
  2. FROM prd_bal
  3. WHERE date = '30-jun-2006'
  4. GROUP BY id, product_id
  5. 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.
Oct 19 '06 #4

P: 1
select count(distinct column1 || column2 || column3) cnt from <tablename> where blah blah...
Mar 5 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.