Thread: 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.