Count City

[ManuNeko]

I have a problem with counting the people in each city. I will give a discription of my tables first and than explain what I want.

City
ID
City
LocationID

People
ID
Name
BirthPlace
DeadPlace
...

What I want is a list of all the cities in LocationID = 1 and a count of all the people that where born there.

Example:
Bazel 12
Belsele 47
Beveren 170
Burcht 1
Daknam 0
De Klinge 9
Doel 1
Eksaarde 6
Elversele 3
Haasdonk 46
Kallo 1
Kemzeke 5
Kieldrecht 5
Kruibeke 13
Lokeren 10
Meerdonk 2
Melsele 13
Moerbeke 12
Nieuwkerken 35
Rupelmonde 2
Sinaai 49
Sint-Gillis 23
Sint-Niklaas 106
Sint-Pauwels 33
Steendorp 3
Stekene 45
Temse 16
Tielrode 3
Vrasene 5
Waasmunster 21
Zwijndrecht 10

My problem is that there are always a few cities missing and I can't seem to get my SQL correct.

Thanks

 

[genghis82]

I found the following worked fine

SELECT City.ID, City.City, City.LocationID, Count(People.Name) AS CountOfName, People.BirthPlace, People.Deathplace
FROM City INNER JOIN People ON City.ID = People.ID
GROUP BY City.ID, City.City, City.LocationID, People.BirthPlace, People.Deathplace
HAVING (((City.LocationID)="Specify location id here"));

Although I think that the following is a better setup as it eradicates the duplication of city names

City
Location ID
City

People
ID
LocationID
BirthPlace
Deathplace

HTH

 

[Ron_dK]

Manu,

I would suggest the following :

Create a one to many relationship between the city field ( City Table) and birthplace ( People table) and create a query something like :

SELECT Count(People.Name) AS CountOfName, People.birthplace
FROM City RIGHT JOIN People ON City.city = People.birthplace
GROUP BY People.birthplace;

Hoop dat dat helpt.

 

[ManuNeko]

genghis82 what you suggests does not give me the empty cities.

What I want is to display all the cities in a certain region (that is the LocationID) and for eacht city the number of people. In my example I want to show all the cities in region 1. That should be 32 cities, but if you count the cities in the example I gave you, you will see that there are only 31! Sometimes I get 15 or some other random number of cities.