Percentage in SQL

[kirsten]

I need to come up with a query that will show the percentage of a certain nationality in a given city

In a table, these are the given fields:
Id number of an individual
city code
nationality code

this is the sql code i have so far:
select distictrow popdata.citynum
count(*) from popdata
where popdata.ancnumber=720
group by popdata.citynum
order by count(*) desc;

-----ancnumber stands for the nationality code, 720 stands for one nationality,for this specific problem 720 means filipino

this sql gives me the number of filipinos in a given city, but i have problem coming up with the total population in a city in another column, and so cant solve the percentage

can anyone please help me? thank you very much!!

 

[PNGBill]

you should copy and past your sql inside the code quotes (#) above.
click on the # and it will give the two quote markers to past your code between.

This removes any transcribing errors which you have in your post.

 

[jtkjames]

In order to calculate the percentage, you need the total population for the given city. Then divide the total by the counted quantity, and format as a percent.

Suggest you do this in two queries

First, make a query to list a count of all individuals in a particular city. Make a parameter for city ID.
Second, make another query, from a table of populations of city IDs. (Ensure the parameter name for the city ID is the same as the first, so Access won't ask you twice.) Then make a third column with a formula as follows:

format([NATIONALITYPOP]/[TOTALPOP], "percent")

where NATIONALITYPOP is the name of your field from the first query, and TOTALPOP is the name of the field in the second query.