Select count of rows in another table in a Postgres SELECT statement

I don't know quite how to phrase this so please help me with the title as well. :)

I have two tables. Let's call them A and B. The B table has a a_id foreign key that points at A.id. Now I would like to write a SELECT statement that fetches all A records, with an additional column containing the count of B records per A row for each row in the result set.

I'm using Postgresql 9 right now, but I guess this would be a generic SQL question?

EDIT:

In the end I went for trigger-cache solution, where A.b_count is updated via a function e开发者_运维技巧ach time B changes.

SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A

I think the comment by @intgr in another answer is so valuable I'm putting forward this as an alternate answer as this method allows you to filter the calculated column efficiently.

SELECT
  a.*,
  COUNT(b.id) AS b_count

FROM a
INNER JOIN b on b.a_id = a.id
WHERE a.id > 50 AND b.ID < 100 -- example of filtering joined tables, optional

GROUP BY a.id
HAVING COUNT(b.id) > 10 -- example of filtering calculated column, optional
ORDER BY a.id

The subquery solution given above is inefficient. The trigger solution is probably best in a mostly-read database, but for the record here's a join approach that will perform better than a subquery:

SELECT a.id, a.xxx, count(*)
FROM a JOIN b ON (b.a_id = a.id)
GROUP BY a.id, a.xxx

If you're using Django ORM you can simply write:

res = A.objects.annotate(Count('b'))
print res[0].b__count  # holds the result count

Accepted answer is inefficient (slow) based on my tests. The subquery of table B executing for every row of table A. I'm using following approach based on grouping and joining. It works much faster:

SELECT A.id, QTY.quantity FROM A
LEFT JOIN
    (SELECT COUNT(B.a_id) AS quantity, B.a_id FROM B GROUP BY B.a_id) AS QTY
ON A.id = QTY.a_id

Another variant:

SELECT A.id, COUNT(B.a_id) AS quantity FROM A
LEFT JOIN B ON B.a_id = A.id
GROUP BY A.id

To answer my own question:

SELECT a.id, a.other_column, ..., 
(SELECT COUNT(*) FROM b where b.a_id = a.id) AS b_count
FROM a;

Whilst a sub-query may be less efficient, how much less efficient depends on the use-case. Another thing to consider is the filters that are being used.

I have a Table A of "Approvers" I have a Table B of "Approval tasks"

I want to show a list of ALL approvers along with a count of how many ACTIVE approval tasks they have. Now, my knowledge of SQL is limited, but no matter what I tried with the different types of join, my list of approvers was incomplete. Why? I need to have a filter on table B so that only active tasks are returned. If an approver only has inactive/complete tasks, there is no count. This should show 0, but for some reason it just doesn't show the row at all.

So, I use a sub-query and it works perfectly.