How to count multiple joined rows in postgres?

Question

I have 4 tables: A, B, C, D.

Table B has a foreign key to A
Table C has a foreign key to A
Table D has a foreign key to C

What I can query so far:

I know how to select all rows from A and count amount of joined rows of B.
I know how to select all rows from A and count amount of joined rows from D (via C).

Now I want to merge those two queries in one select and show there count_b and count_d. But the resulting select shows wrong count. Here's how I do it:

SELECT
  "A"."id",
  COUNT("B"."id") AS "B_count",
  COUNT("D"."id") AS "D_count"
FROM "users" AS "A" LEFT OUTER JOIN "B" AS "B"
    ON "A"."id" = "B"."a__id"
  LEFT OUTER JOIN "C" AS "C"
    ON "A"."id" = "C"."a_id"
  LEFT OUTER JOIN "D" AS "D"
    ON "C"."id" = "D"."c_id"
GROUP BY "A"."id"

There's probably the issue with GROUP BY. IT seems I need to write separate group by for each count. I'm looking for result without nested selects (because I still need to map this to ORM, dont ask why). Is it possible to archive with distinct by?

try with count(distinct D) and count(distinct B)

McNets
– McNets

2017-03-28 15:14:12 +00:00
Commented Mar 28, 2017 at 15:14 — McNets
– McNets, Commented Mar 28, 2017 at 15:14

McNets · Accepted Answer · 2017-03-28 15:28:49Z

19

Due you are joining 3 tables, you should use DISTINCT within COUNT:

SELECT
  "A"."id",
  COUNT(DISTINCT "B"."id") AS "B_count",
  COUNT(DISTINCT "D"."id") AS "D_count"
FROM "users" AS "A" LEFT OUTER JOIN "B" AS "B"
    ON "A"."id" = "B"."a__id"
  LEFT OUTER JOIN "C" AS "C"
    ON "A"."id" = "C"."a_id"
  LEFT OUTER JOIN "D" AS "D"
    ON "C"."id" = "D"."c_id"
GROUP BY "A"."id"

answered Mar 28, 2017 at 15:28

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mpowered Over a year ago

The DISTINCT was what I was missing :-)

Collectives™ on Stack Overflow

How to count multiple joined rows in postgres?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related