Calculating % Change and Sum function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with columns for County, Zip, Age Group, and population for
three different years. I would like to create a query to calculate percent
change in population, and then sum by county to determine which county has
the highest percent growth in the 65+ Age group. I know how to filter the
data to only get the 65+ group, but can't figure out how to sum by county and
calculate % change. Thanks in advance for any help anyone might have to
offer!

Angie
 
Angie:

Presumably you must also have a column for the sample year for each row. I
don't really see where a SUM operation would come into it, though. Try
something like this:

SELECT
T1.SampleYear, T1.County, T1.AgeGroup
(T1.Population -
(SELECT T2.Popluation
FROM YourTable AS T2
WHERE T2.County = T1.County
AND T2.AgeGroup = T1.AgeGroup
AND T2.SampleYear = T1.SampleYear-1)) /
(SELECT T3.Popluation
FROM YourTable AS T3
WHERE T3.County = T1.County
AND T3.AgeGroup = T1.AgeGroup
AND T3.SampleYear = T1.SampleYear-1) *
100 AS PercentGrowth
FROM YourTable As T1
WHERE T1.AgeGroup = "65+";

That should give you the year-on-year growth rates. If you want the growth
rate over the three years then use SampleYear-2 rather than SampleYear-1 as
the criteria in the subqueries and additionally restrict the outer query to
where the sample year is the latest year.

If you sort the result set of the query by PercentGrowth DESC this will
return the rows with the highest growth county first and the lowest last.

Ken Sheridan
Stafford, England
 
Back
Top