Re: query problem - get count in related table

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