CODEWITHSUNDEEP
sqlcountgroup-bysumaggregate-functions
koryakinp
koryakinp

Reputation: 4125

SUM() of COUNT() in SQL query

I have following tables:

Persons:

PersonID(PK)
AppartmentID(FK)
Name

Appartments:

AppartmentID(PK)
BuildingID(FK)
ApartmentNumber

Buildings:

BuildingID
Address

I am trying to get how many people live in each building, something like that:

| Address  | Sum |
|----------|-----|
| Address1 | 169 |
| Address2 | 353 |
| Address3 | 232 |
| Address4 | 199 |

The best query I got so far is:

SELECT 
   Buildings.BuildingID,
   sum(c)
FROM
   (SELECT 
        COUNT(*) as c 
    FROM 
        Persons
    GROUP BY 
        Persons.AppartmentID) as c, Appartments
GROUP BY 
    Buildings.BuildingID;

But it is not working as I expect.

Any help would be much appreciated.

Upvotes: 0

Views: 118

Answers (1)

juergen d
juergen d

Reputation: 204854

SELECT b.BuildingID, 
       b.Address, 
       count(p.personID) as persons
FROM Buildings b
LEFT JOIN Appartments a ON a.BuildingID = b.BuildingID
INNER JOIN Persons p ON p.AppartmentID = a.AppartmentID
GROUP BY b.BuildingID, b.Address

Upvotes: 1

Related Questions