pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCounting rows from two tables in one query
3+ messages / 3 participants
[nested] [flat]
* Counting rows from two tables in one query
@ 2004-04-07 10:06 Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
0 siblings, 2 replies; 3+ messages in thread
From: Stefan Weiss @ 2004-04-07 10:06 UTC (permalink / raw)
To: pgsql-sql
Hi.
I have a (simplified) table layout like this:
+---------+ +---------+
| sub_a | +------+ | sub_b |
+---------+ | main | +---------+
| id | +------+ | id |
| main_id | ----> | id | <---- | main_id |
| ... | | ... | | ... |
+---------+ +------+ +---------+
What I am trying to get is a list that shows how many records from
'sub_a' and 'sub_b' are referencing 'main':
main_id | count_a | count_b
---------+---------+---------
1 | 2 | 1
2 | 12 | 1
3 | 7 | 3
[......]
This query obviously does not do what I need, it gives me the product
of count_a and count_b in both columns instead:
select main.id as main_id,
count(sub_a.*) as count_a,
count(sub_b.*) as count_b
from main,
sub_a,
sub_b
where sub_a.main_id = main.id
and sub_b.main_id = main.id
group by main.id
having count(sub_a.*) > 0
and count(sub_b.*) > 0
;
Is it possible to get a list like the one above with a single query?
thanks,
stefan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Counting rows from two tables in one query
@ 2004-04-10 01:28 Greg Sabino Mullane <greg@turnstep.com>
parent: Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Sabino Mullane @ 2004-04-10 01:28 UTC (permalink / raw)
To: pgsql-sql; +Cc: spaceman-75e1f-20040422@ausgehaucht.sensenmann.at
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> What I am trying to get is a list that shows how many records from
> 'sub_a' and 'sub_b' are referencing 'main':
SELECT id,
(SELECT COUNT(*) FROM sub_a WHERE sub_a.main_id=main.id),
(SELECT COUNT(*) FROM sub_b WHERE sub_b.main_id=main.id)
FROM main ORDER BY id;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404092128
-----BEGIN PGP SIGNATURE-----
iD8DBQFAd03pvJuQZxSWSsgRAuDuAJ0elm2bPjgC1bGPHnrotzXrPKCt4ACdFytf
BglMm6IouFFZt1c19zST5ac=
=7DbY
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Counting rows from two tables in one query
@ 2004-04-10 15:09 Bruno Wolff III <bruno@wolff.to>
parent: Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2004-04-10 15:09 UTC (permalink / raw)
To: Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>; +Cc: pgsql-sql
On Wed, Apr 07, 2004 at 10:06:20 +0000,
Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at> wrote:
> Hi.
>
> What I am trying to get is a list that shows how many records from
> 'sub_a' and 'sub_b' are referencing 'main':
>
> main_id | count_a | count_b
> ---------+---------+---------
> 1 | 2 | 1
> 2 | 12 | 1
> 3 | 7 | 3
> [......]
>
> This query obviously does not do what I need, it gives me the product
> of count_a and count_b in both columns instead:
>
> select main.id as main_id,
> count(sub_a.*) as count_a,
> count(sub_b.*) as count_b
> from main,
> sub_a,
> sub_b
> where sub_a.main_id = main.id
> and sub_b.main_id = main.id
> group by main.id
> having count(sub_a.*) > 0
> and count(sub_b.*) > 0
> ;
>
> Is it possible to get a list like the one above with a single query?
You want something along the lines of:
SELECT main.id, a.acnt, b.bcnt
FROM main LEFT JOIN
(SELECT main_id, count(*) AS acnt FROM sub_a GROUP BY main_id) AS a
ON (main.id = a.main_id)
LEFT JOIN
(SELECT main_id, count(*) AS acnt FROM sub_b GROUP BY main_id) AS b
ON (main.id = b.main_id)
;
I haven't tested the above, so there might be some minor problem with it.
Also depending on how you want to handle cases where there are no
references to an id in main in either sub_a and/or sub_b, you may need
to modify the query. The way it is now, you will get NULLs in those
cases instead of zero.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-04-10 15:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-07 10:06 Counting rows from two tables in one query Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
2004-04-10 01:28 ` Greg Sabino Mullane <greg@turnstep.com>
2004-04-10 15:09 ` Bruno Wolff III <bruno@wolff.to>
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