Calculating Percentages in a Query

[Guest]

I am trying to build what I think is a simple database. It will be used to
track how frequently students wear a school uniform. Up until now, this
information has been gathered by hand.

Presently, I have two tables:
Homeroom: master list of homeroom numbers (key)
Uniform_Records: auto id (key), homeroom (linked to Homeroom table), Date
(captured as the timestamp), Present (records # of students present),
in_uniform (records how many students were in uniform that day)

Teachers will submit this information via our intranet.

What I am trying to do now is build the queries which I can use to display
the results via a web page.

What I want to be able to do is list each homeroom and the percentage of
students who wore the uniform for the week. A couple of things for me to
consider:
1. I would like this to be a running results table. This query would be set
up each Monday so classes could see the results throughout the week.

2. Should a homeroom not submit its data, the homeroom should still be
listed with a 0%.

I envision the results looking like:

Percentage of Students Wearing Uniforms
Homeroom %
01 43
02 53
03 68
04 23
05 0
06 48
etc.

Can this be created in one query or will I need multiples? Can someone help
me along?

I do not seem to be able to even get the counts by homeroom, let alone the %
per homeroom.

I would appreciate any help you can provide.

 

[James McNellis]

You can do this with one query.

SELECT homeroom, date, present, in_uniform, (in_uniform / present) AS
percent FROM uniform_records

Or, if you want the percent for a homeroom without regard to dates...

SELECT homeroom, SUM(present) AS presentTotal, SUM(in_uniform) AS
inUniformTotal, (SUM(in_uniform) / SUM(present)) AS perecntTotal FROM
uniform_records GROUP BY homeroom

Cheers--

James.

 

[Guest]

You can do this with one query.

SELECT homeroom, date, present, in_uniform, (in_uniform / present) AS
percent FROM uniform_records

This does calculate the percentage per record. Thanks.

The table will have many records for the same homeroom. What I want to do
is combine all the data for the classroom within a date range and calculate
the percentage.

Homeroom 01 should have a record for each day of the week, each week. I
want the query to be able to set the date range, gather all the data for each
classroom (even those who may not submit its data) and calculate the
percentage of students wearing uniforms. I do not seem to be able to combine
all the homeroom 01 data within the date range to be able to do that.

 

[James McNellis]

The second query I mentioned will calculate over the entire period recorded.

To calculate for a specific period:

SELECT homeroom, DATEPART('m', date) AS month, SUM(present) AS presentTotal,
SUM(in_uniform) AS
inUniformTotal, (SUM(in_uniform) / SUM(present)) AS perecntTotal FROM
uniform_records GROUP BY homeroom, month

I am fairly certain this will work. If not, then replace month in the GROUP
BY statement with DATEPART('m', date)

'ww' instead of 'm' will query by week. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2mic.asp

HTH--

James.

 

[Douglas J Steele]

You should include checks to ensure that present isn't 0...

To report on homerooms which haven't submitted their data, it's necessary to
do a LEFT JOIN between the two tables.

Now, I'm not 100% certain what's meant by point 1 below:

1. I would like this to be a running results table. This query would be set
up each Monday so classes could see the results throughout the week.

Assuming that you want to show the results for multiple days, the simplest
may be to have a rolling 7 day display. Something like the following
untested SQL should work:

SELECT homeroom.homeroom,
Format(uniform_records.reporting_date, "ddd") AS Weekday
SUM(Nz(present, 0)) AS presentTotal,
SUM(Nz(in_uniform, 0)) AS inUniformTotal,
IIf(SUM(Nz(present, 0)) = 0, 0, (SUM(Nz(in_uniform,0)) /
SUM(Nz(present,0)))) AS percentTotal
FROM homeroom LEFT JOIN uniform_records
ON homeroom.homeroom = uniform_records.homeroom
WHERE uniform_records.reporting_date BETWEEN DateDiff("d", -7, Date()) AND
Date()
GROUP BY homeroom.homeroom, Format(uniform_records.reporting_date, "ddd")