Re: Count the children

From: Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Count the children
Date: 2009-08-19 00:12:06
Message-ID: A6A360DD-7CB4-4B37-824B-18086A919DA6@internode.on.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(dot)rowan(at)internode(dot)on(dot)net

11 Kingscote Street
ALBERTON
South Australia 5014

tel 618 8240 3993

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jignesh Shah 2009-08-19 05:42:47 Re: The efficient method to create trigger
Previous Message The PandaWare Company 2009-08-18 22:46:23 Re: su not working with psql