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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-05-24 18:24:17 | Re: Using CASE with a boolean value |
Previous Message | Tom Ansley | 2002-05-24 16:15:55 | Re: Using CASE with a boolean value |