Обсуждение: One-2-many relation - need distinct counts
Hi gurus, I have table A, B and need a distinct count of Accounts from A. There is a one-2-many relation between A and B. Accounts # in A are all unique. However, in B there will be duplicates. So the problem I have is, when I join as follows: Select Count(Distinct(account_no)) from A, B where A.Account_no = B.Account_no I get the correct count. If I do this: Select Count(Distinct(account_no)), B.Account_type from A, B where A.Account_no = B.Account_no group by B.Account_type I get wrong counts because there some are duplicated. I tried everything that I can think of - subquery, sub table etc. I would appreciate some help in writing the query. Thanks in advance. PS __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
On Tuesday 26 August 2003 14:54, PS PS wrote: > Select Count(Distinct(account_no)) > from A, B > where A.Account_no = B.Account_no > > I get the correct count. If I do this: > Select Count(Distinct(account_no)), B.Account_type > from A, B > where A.Account_no = B.Account_no > group by B.Account_type > > I get wrong counts because there some are duplicated. > I tried everything that I can think of - subquery, sub > table etc. I would appreciate some help in writing > the query. Thanks in advance. I'm not sure the query is well formed. If you have the following in B: Acct_type | Acct_no alpha | 0001 beta | 0002 alpha | 0003 beta | 0003 I think you're saying you get: alpha 2 beta 2 Are you saying you want alpha 2 beta 1 or: alpha 1 beta 2 If you're not sure which you want, that's the route of your problem. If you want the first try something like SELECT account_no, min(account_type) FROM B GROUP BY account_no -- Richard Huxton Archonet Ltd