SQL returning all rows and count

Question

I have 3 tables, Property managers, Addresses and Units. I'm trying to get all the rows using the aggregate function COUNT, but it only returns 1 row.

I want to get all the addresses where pmNo = 1 and the total count of units for each address. My SQl query is like this:

SELECT a.*, COUNT(u.unitId) AS totalUnits
FROM Units AS u
JOIN Addresses AS a ON a.addressNo = u.addressNo
JOIN PropertyManagers AS pm ON pm.pmNo = a.pmNo
WHERE pm.pmNo = 1

This gives me the result of:

addressNo     city     province     postalCode     buildingName     pmNo     totalUnits
1             city1      prov1          V9F3b6     building1 Apt.   1            3

Which is right but it only gives me the first address fields then combines both the addresses units into the total units.

I want the result to be:

addressNo     city     province     postalCode     buildingName     pmNo     totalUnits
1             city1      prov1          V9F3b6     building1 Apt.   1            2
2             city2      prov2          V0N1U8     building2 Apt.   1            1

Answer 1

Aggregation functions like COUNT for non-simple cases need to specify the aggregation that is happen. In your case, you want the addresses. As addressNo is the primary key and the easiest identifier of a unique address use the GROUP BY of the query:

SELECT a.*, COUNT(u.unitId) AS totalUnits
FROM Units AS u
JOIN Addresses AS a ON a.addressNo = u.addressNo
JOIN PropertyManagers AS pm ON pm.pmNo = a.pmNo
WHERE pm.pmNo = 1
GROUP BY a.addressNo