SQL query help: total people living in each province

byMR

February 13, 2022

I am using PostgreSQL and Windows.

I have an SQL query issue for which I need some help.

The table diagram:

I am having difficulty trying to understand this scenario:

Return the list of provinces alphabetically ordered, and the total count of personnel living in each province where there are at least 5 personnel living in it.

The answer should look something like this:

Would really appreciate if someone can please tell me what the query will be.

>Solution :

I see this as being a join between the Province and Personnel tables:

SELECT pv.Code, pv.Name, COUNT(pl.PersonnelID) AS "Total Personnel"
FROM Province pv
LEFT JOIN Personnel pl
    ON pl.CountryOfOrigin = pv.CountryID
GROUP BY pv.Code, pn.Name;

Note that I am including the province code in the query, as perhaps two provinces might occasionally share the same name. If not, then you may remove the code and use the structure of your current output table.