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

Question

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