Re: query problem - get count in related table - Mailing list pgsql-novice

On Fri, 24 May 2002 00:14:59 +0100, Rory Campbell-Lange
<rory@campbell-lange.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