Percentage in MS Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Percentage in MS Access

Author

Topic

giafortun
Starting Member

1 Post

Posted - 2010-09-07 : 02:44:57

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!!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-07 : 08:14:37

here's one way


SELECT MAX(anc) / MAX(total) FROM (
SELECT 0 as anc, count(*) as total from popdata
union all
SELECT count(*) as anc, 0 as total from popdata where ancnumber = 720
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources