SQL query: total people living in each province-CodePudding

I am using PostgreSQL and Windows.

I have an SQL query issue. 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:

How can I write a query for this?

CodePudding user response：

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.