What Postgres query can I use for the below scenario?
Parent table:
Child table:
Expected result
Explanation:
Parent1 --> 4 equipments. Parent1 has ...
Parent2 --> 3 equipments. Parent2 has ...
Basically, aggregate after joining:
SELECT p.parent AS name, count(*)
FROM parent p
JOIN child c USING (name)
GROUP BY p.parent
ORDER BY p.parent;
Nuances of the query depend on undisclosed details.
Like, to include parents with no equipment, and optimize performance for possibly many rows per child, use a LEFT JOIN, and aggregate counts per child before the join:
SELECT p.parent AS name, sum(c.ct) AS count
FROM parent p
LEFT JOIN (
SELECT c.name, count(*) AS ct
FROM child c
GROUP BY 1
) c USING (name)
GROUP BY 1
ORDER BY 1
