Useful to add a title to your messages before you post...
How about:
select parentid, count(*) as number_of_children
from childtable
group by parentid
order by parentid;
If there are parent records that have no children then these will be
omitted. The query above totally ignores the parent table (which you may
not want it to do).
John Sidney-Woollett
Joost Kraaijeveld wrote:
> Hi all,
>
> Is it possible to count and display the number of children of a parent in a generic query?
>
>
> parent table: id
> child table: id, parent_id
>
> Example output of the query:
>
> parentid number_of_children
> parent1 2
> parent2 6
> parent3 0
>
>
>
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly