CODEWITHSUNDEEP
postgresqljoin
deathangel908
deathangel908

Reputation: 9699

How to count multiple joined rows in postgres?

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

What I can query so far:

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?

Upvotes: 3

Views: 7130

Answers (1)

McNets
McNets

Reputation: 10807

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"

Upvotes: 19

Related Questions