From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Mike Orr'" <sluggoster(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Counting records in a child table |
Date: | 2011-03-31 19:01:23 |
Message-ID: | 008801cbefd6$07e46700$17ad3500$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Mike Orr
Sent: Thursday, March 31, 2011 2:49 PM
To: pgsql-general(at)postgresql(dot)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 <sluggoster(at)gmail(dot)com>
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Clark | 2011-03-31 19:07:51 | Re: 7.4 quoting |
Previous Message | Mike Orr | 2011-03-31 18:48:40 | Counting records in a child table |