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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
