Thanks. How would I do it with a window function? I thought windows only compared groups of records in the same table. On Thu, Mar 31, 2011 at 12:01 PM, David Johnston <polobo@xxxxxxxxx> wrote: > An alternative: > > SELECT > parent.*, > COALESCE(child.childcount, 0) AS whatever > FROM parent > LEFT JOIN > (SELECT parentid, count(*) as childcount FROM child GROUP BY parented) child > ON (parent.id = child.parentid) > > You could also do: > SELECT parent.*, > COALESCE((SELECT count(*) FROM child WHERE child.id = parent.id),0) AS > childcount --coalesce may not be necessary.... > FROM parent > > Window Functions can also give appropriate results. > > I am not positive whether COUNT(*) excludes NULL during its count but a > quick documentation search or just trying it will tell you that. > > David J. > > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Mike Orr > Sent: Thursday, March 31, 2011 2:49 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Counting records in a child table > > I know how to do count(*)/group by on a single table, but how do I get a > count of related records in a child table? Some of the counts will be zero. > > SELECT > parent.id AS id, > parent.name AS name, > parent.create_date AS create_date, > COUNT(child.id) AS count > FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY > parent.id, parent.name, parent.create_date ORDER by count desc; > > Is this correct, and is it the simplest way to do it? > > I used a left join to avoid skipping parent records that have no child > records. I grouped by parent.id because those are the result rows I want. I > added the other group by fields because psql refused to run the query > otherwise. > > -- > Mike Orr <sluggoster@xxxxxxxxx> > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- Mike Orr <sluggoster@xxxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
