Thread: Count the children

I need what sounds like a very simple query - but I'm darned if I can
figure it out.

I have two tables, parent and child related by child.parent_id =
parent.id
The query should return, for each parent row, parent.name and number
of child rows

Can someone enlighten me, please?

Michael Rowan
mike.rowan@internode.on.net

11 Kingscote Street
ALBERTON
South Australia 5014

tel 618 8240 3993

On Wed, Aug 19, 2009 at 12:04:13AM +0930, Michael Rowan wrote:
> I need what sounds like a very simple query - but I'm darned if I can
> figure it out.
>
> I have two tables, parent and child related by child.parent_id =
> parent.id
> The query should return, for each parent row, parent.name and number of
> child rows
>
> Can someone enlighten me, please?

Assuming parent.name is unique:

SELECT parent.name, count(*)
FROM   parent, child
WHERE  parent.key = child.parent_key
GROUP BY parent.name;

--
Mark
http://www.lambic.co.uk

Thanks to all who replied.  I am an absolute newbie here.

Of course, parent.name is not unique, so I use parent.id instead and
all is well.  Except that I need to return columns from the parent
table such as name, phone etc.

I assume this can be done using a subquery, but have no idea how it
should be.

Any offers?

Mike
On 19/08/2009, at 12:14 AM, Mark Styles wrote:

> On Wed, Aug 19, 2009 at 12:04:13AM +0930, Michael Rowan wrote:
>> I need what sounds like a very simple query - but I'm darned if I can
>> figure it out.
>>
>> I have two tables, parent and child related by child.parent_id =
>> parent.id
>> The query should return, for each parent row, parent.name and
>> number of
>> child rows
>>
>> Can someone enlighten me, please?
>
> Assuming parent.name is unique:
>
> SELECT parent.name, count(*)
> FROM   parent, child
> WHERE  parent.key = child.parent_key
> GROUP BY parent.name;
>
> --
> Mark
> http://www.lambic.co.uk
>

Michael Rowan
mike.rowan@internode.on.net

11 Kingscote Street
ALBERTON
South Australia 5014

tel 618 8240 3993