percentages in various groups

[feh]

Hi,

I've a table organized by states, years, subjects,
grades, levels and an associated student count.

I want to find the percentage of students in each
year, subject, grade and level.

This is what I'm working with, but don't really
know what to do next.


SELECT EXTR046_ST.[State Name],
EXTR046_ST.[Reporting Period],
EXTR046_ST.Subject,
EXTR046_ST.[Grade Level],
EXTR046_ST.[Performance Level],
EXTR046_ST.[Student count],

Sum (EXTR046_ST.[Student count]) AS [SumStudents]
* I KNOW MORE STUFF BELONGS HERE BUT NOT SURE *

FROM EXTR046_ST where EXTR046_ST.[state name] = "STATE"

GROUP BY EXTR046_ST.[State Name],
EXTR046_ST.[Reporting Period],
EXTR046_ST.Subject,
EXTR046_ST.[Grade Level],
EXTR046_ST.[Performance Level];

Thanks in advance,
Charles

 

[John Spencer]

You don't say percentage of students as what part of what whole.

Student Count divided by all students in the table?
Student Count Divided by all students in the state?
Student Count Divided by all students in the state and for the Reporting
Period?

SELECT EXTR046_ST.[State Name],
EXTR046_ST.[Reporting Period],
EXTR046_ST.Subject,
EXTR046_ST.[Grade Level],
EXTR046_ST.[Performance Level],
EXTR046_ST.[Student count],

[Student Count]/
SELECT Sum([Student count])
FROM EXTR046_ST
WHERE [State Name] = "State") as TheResult

FROM EXTR046_ST where EXTR046_ST.[state name] = "STATE"


You could build a separate query to get the sum of the count by whatever
grouping level you wanted and then join the query to Extr046 by the
appropriate fields.


SELECT EXTR046_ST.[State Name],
EXTR046_ST.[Reporting Period],
EXTR046_ST.Subject
Sum (EXTR046_ST.[Student count]) AS [SumStudents]

FROM EXTR046_ST

GROUP BY EXTR046_ST.[State Name],
EXTR046_ST.[Reporting Period],
EXTR046_ST.Subject

Then
SELECT EXTR046_ST.[State Name],
EXTR046_ST.[Reporting Period],
EXTR046_ST.Subject,
EXTR046_ST.[Grade Level],
EXTR046_ST.[Performance Level],
EXTR046_ST.[Student count]
,[Student Count]/SumStudents
FROM EXTR046 INNER JOIN qCountByStatePeriodSubject as Q
ON EXTR046.[State Name] = Q.[State Name]
AND EXTR046.[Reporting Period] = Q.[Reporting Period]
AND EXTR046.Subject = Q.Subject




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================