Обсуждение: 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@gmail.com>
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@postgresql.org
[mailto:pgsql-general-owner@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 <sluggoster@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
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@yahoo.com> 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@postgresql.org > [mailto:pgsql-general-owner@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 <sluggoster@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 > > -- Mike Orr <sluggoster@gmail.com>
Not fully sure on the syntax of the Window to accomplish the specified goal - and am not sure it would be any cleaner anyway. But, the reason I am responding is how you phrased "...windows only compared groups of records in the same table". When I say: FROM tableA * JOIN tableB I have now effectively created a new "table" in the sense that anything I can do on tableA or tableB I can also do on the result of the joining of those two tables. Re-reading the comment and context it probably would be more fair to guess that you know this. In the context of a Window function you can use it AFTER you perform a LEFT JOIN - but you may not need to do the "SELECT id, count(*) GROUP BY id" on the child table but instead can just do a direct left join onto child and use a Window. Again, mostly just speculation and for the moment I don't have the time to spare to try out a Window based solution; especially since my gut says the LEFT JOIN on the grouped child is likely the best solution anyway. David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Orr Sent: Thursday, March 31, 2011 3:20 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Counting records in a child table Thanks. How would I do it with a window function? I thought windows only compared groups of records in the same table.