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-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mike Orr Sent: Thursday, March 31, 2011 2:49 PM To: pgsql-general@postgresql.org Subject: [GENERAL] 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 <sluggos...@gmail.com> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
