Skip site navigation (1) Skip section navigation (2)

Re: query problem - get count in related table

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
Cc: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>,Postgresql Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: query problem - get count in related table
Date: 2002-05-24 17:22:29
Message-ID: 4dtseuon8a5dvp0cuurmr4vth30e8chim6@4ax.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, 24 May 2002 00:14:59 +0100, Rory Campbell-Lange
<rory(at)campbell-lange(dot)net> wrote:

>I'm still looking to
>get a result like this:
>
>		 id | data | count 
>		----+------+-------
>		  1 |    2 |     3
>		  2 |    1 |     2
>		  3 |    4 |     1
>		  5 |    2 |     0
>		(3 rows)
>
>brandf=# select * from a;    brandf=# select * from b;
> id | data                    id 
>----+------                  ----
>  1 |    2                     2
>  2 |    1                     2
>  3 |    4                     1
>  5 |    2                     1
>(4 rows)                       1
>                               3
>                             (6 rows)                 
Rory,

assuming all your a.id are NOT NULL and unique:

	SELECT a.id, a.data, count(b.id)
	FROM a LEFT JOIN b ON a.id = b.id
	GROUP BY a.id, a.data;

HTH.
Servus
 Manfred

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2002-05-24 18:24:17
Subject: Re: Using CASE with a boolean value
Previous:From: Tom AnsleyDate: 2002-05-24 16:15:55
Subject: Re: Using CASE with a boolean value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group