Get count of foreign key from multiple tables

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

Answer

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.