SQL returning all rows and count-CodePudding

I have 3 tables:

CREATE TABLE PropertyManagers(
    pmNo    INT NOT NULL AUTO_INCREMENT,
    userNo  INT,
    PRIMARY KEY(pmNo),
    FOREIGN KEY(userNo) REFERENCES Users(userNo)
);

CREATE TABLE Addresses(
    addressNo       INT NOT NULL,
    city            varchar(30),
    province        varchar(5),
    postalCode      varchar(6),
    buildingName    varchar(20),
    pmNo            INT,
    PRIMARY KEY(addressNo),
    FOREIGN KEY(pmNo) REFERENCES PropertyManagers(pmNo)
);

CREATE TABLE Units(
    unitId  INT NOT NULL AUTO_INCREMENT,
    unitNo  INT NOT NULL,
    userNo  INT,
    addressNo   INT,
    PRIMARY KEY(unitId),
    FOREIGN KEY(userNo) REFERENCES Users(userNo),
    FOREIGN KEY(addressNo) REFERENCES Addresses(addressNo)
);

I have 2 addresses where the pmNo is 1, and 3 total units for both these addresses, 2 units where addressNo = 1 and 1 unit where addressNo = 2.

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

CodePudding user response：

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