Get count of foreign key from multiple tables

aggregate-functions, correlated-subquery, left-join, postgresql, sql

Solution

If the query involves large parts of `b` and / or `c` it is more efficient to aggregate first and join later. I expect these two variants to be considerably faster:

SELECT a.id,
     , COALESCE(b.ct, 0) + COALESCE(c.ct, 0) AS bc_ct
FROM   a
LEFT   JOIN (SELECT a_id, count(*) AS ct FROM b GROUP BY 1) b USING (a_id)
LEFT   JOIN (SELECT a_id, count(*) AS ct FROM c GROUP BY 1) c USING (a_id);

You need to account for the possibility that some `a_id` are not present at all in `a` and / or `b`. `count()` never returns `NULL`, but that's cold comfort in the face of `LEFT JOIN`, which leaves you with `NULL` values for missing rows nonetheless. You must prepare for `NULL`. Use `COALESCE()`.

Or UNION ALL `a_id` from both tables, aggregate, then JOIN:

SELECT a.id
     , COALESCE(ct.bc_ct, 0) AS bc_ct
FROM   a
LEFT   JOIN (
   SELECT a_id, count(*) AS bc_ct
   FROM (
      SELECT a_id FROM b
      UNION ALL
      SELECT a_id FROM c
      ) bc
   GROUP  BY 1
   ) ct USING (a_id);

Probably slower. But still faster than solutions presented so far. And you could do without `COALESCE()` and still not loose any rows. You might get occasional `NULL` values for `bc_ct`, in this case.

Problem

I have 3 tables, with Table B & C referencing Table A via Foreign Key. I want to write a query in PostgreSQL to get all ids from A and also their total occurrences from B & C. ``` a | b | c ----------------------------------- id | txt | id | a_id | id | a_id ---+---- | ---+----- | ---+------ 1 | a | 1 | 1 | 1 | 3 2 | b | 2 | 1 | 2 | 4 3 | c | 3 | 3 | 3 | 4 4 | d | 4 | 4 | 4 | 4 ``` Output desired (just the id from A & total count in B & C) : ``` id | Count ---+------- 1 | 2 -- twice in B 2 | 0 -- occurs nowhere 3 | 2 -- once in B & once in C 4 | 4 -- once in B & thrice in C ``` SQL so far SQL Fiddle : ``` SELECT a_id, COUNT(a_id) FROM ( SELECT a_id FROM b UNION ALL SELECT a_id FROM c ) AS union_table GROUP BY a_id ``` The query I wrote fetches from B & C and counts the occurrences. But if the key doesn't occur in B or C, it doesn't show up in the output (e.g. id=2 in output). How can I start my selection from table A & join/union B & C to get the desired output

Original source