pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOne-2-many relation - need distinct counts
2+ messages / 2 participants
[nested] [flat]
* One-2-many relation - need distinct counts
@ 2003-08-26 13:54 PS PS <psus2020@yahoo.com>
2003-08-27 19:51 ` Re: One-2-many relation - need distinct counts Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: PS PS @ 2003-08-26 13:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: One-2-many relation - need distinct counts
2003-08-26 13:54 One-2-many relation - need distinct counts PS PS <psus2020@yahoo.com>
@ 2003-08-27 19:51 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2003-08-27 19:51 UTC (permalink / raw)
To: PS PS <psus2020@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-08-27 19:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-26 13:54 One-2-many relation - need distinct counts PS PS <psus2020@yahoo.com>
2003-08-27 19:51 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox