From: Bruno Wolff III <bruno@wolff.to>
To: Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Counting rows from two tables in one query
Date: Sat, 10 Apr 2004 10:09:09 -0500
Message-ID: <20040410150909.GB22991@wolff.to> (raw)
In-Reply-To: <1928683.cthc5y6rJJ@weyoun.foo.at>
References: <1928683.cthc5y6rJJ@weyoun.foo.at>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, spaceman-75e1f-20040422@ausgehaucht.sensenmann.at
Subject: Re: Counting rows from two tables in one query
In-Reply-To: <20040410150909.GB22991@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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