Re: Select number of children of a parent query

You might need to read a good SQL primer to get a full explanation of
this feature.

Reading from the docs,
http://www.postgresql.org/docs/7.4/interactive/sql-select.html

<quote>
The optional GROUP BY clause has the general form

GROUP BY expression [, ...]

GROUP BY will condense into a single row all selected rows that share
the same values for the grouped expressions. expression can be an input
column name, or the name or ordinal number of an output column (SELECT
list item), or an arbitrary expression formed from input-column values.
In case of ambiguity, a GROUP BY name will be interpreted as an
input-column name rather than an output column name.

Aggregate functions, if any are used, are computed across all rows
making up each group, producing a separate value for each group (whereas
without GROUP BY, an aggregate produces a single value computed across
all the selected rows). When GROUP BY is present, it is not valid for
the SELECT list expressions to refer to ungrouped columns except within
aggregate functions, since there would be more than one possible value
to return for an ungrouped column.
</quote>

John Sidney-Woollett

Joost Kraaijeveld wrote:

> Hi John,
>
> John Sidney-Woollett schreef:
>
>>Useful to add a title to your messages before you post...
>
> It escaped before finishing.....
>
>
>>How about:
>>
>>select parentid, count(*) as number_of_children
>>from childtable
>>group by parentid
>>order by parentid;
>
> It works but can you tell me why this works? Is the count(*) over the "group by parentid"?
>
>
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J(dot)Kraaijeveld(at)Askesis(dot)nl
> web: www.askesis.nl