X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5130CD1D9F6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Apr 2004 12:05:37 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 23954-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Apr 2004 12:05:34 -0300 (ADT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id 4C6DDD1D9C2
	for <pgsql-sql@postgresql.org>; Sat, 10 Apr 2004 12:05:34 -0300 (ADT)
Received: (qmail 24084 invoked by uid 500); 10 Apr 2004 15:09:09 -0000
Date: Sat, 10 Apr 2004 10:09:09 -0500
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
Message-ID: <20040410150909.GB22991@wolff.to>
Mail-Followup-To: Stefan Weiss
	<spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>,
	pgsql-sql@postgresql.org
References: <1928683.cthc5y6rJJ@weyoun.foo.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1928683.cthc5y6rJJ@weyoun.foo.at>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200404/90
X-Sequence-Number: 17465

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.