calculating percentage values..

[saleemMSMS]

consider the following table

i want to query and take the total population and the number of GNs group by the district so i wrote the following query

SELECT District.DistrictName AS [District Name], Count(GN.GNCode) AS [Number of GNs], sum(population) AS [Total Polulation]
FROM (District INNER JOIN GN ON District.DisctrictCode=GN.DisctrictCode)
GROUP BY (District.DisctrictCode), District.DistrictName;

but now i want the percentage of population in each district.. can someone guide me towards the query? since theres a group by clause i'm unable to get the total population.. thats what is worrying.. any suggestions please ?

 

[ajetrumpet]

can't be done in one query I don't think. try two. or better yet, try to do this stuff on your display object. if you do that, then you'll only need one, or maybe even none of the queries and you just pull the data out of this thing and display it with code only. why not do that?

but...the query for line %ages is this:

SELECT 

population / dsum("population", "this table") AS % of pop

for total %ages, use the JOIN again and write this:

SELECT table 1.district code, table 2.population, 

dsum("population", "table 2", "[districtcode] = " & [districtcode]) / 

dsum("population", "table 2") as % of total population

FROM table 1

JOIN ON field = field

 

[namliam]

First make a query to count and sum all, then add this query (joined) into this one.

Then it has become a simple calculation