Building a query to calculate percentages

[Guest]

H,

I'm having a problem creating a query that will be used for a stacked chart.
Here's a small example of the data in the table.

Class Teacher Rating
7787 Jones 1
8787 Smith 2
3434 Jones 2
4343 Larson 3
8787 Jones 2
9999 Jones 2
What I would like to do is create a query that will show the teachers name
along with the percentage of the total for each rating.

Example Teacher Jones has a total of 4 ratings, what percent of the
4entries are rated as 1, 2, or 3.

I think I'm making it more complicated than it has to be. Any help is
greatly appreciated. Thanks a lot.

BL

 

[Tom Ellison]

Dear BL:

So, Jones has 4 ratings, 3 of which are "2" and 1 is "1". So, you would
have 25% "1" and 75% "2". Do I have that right.

You can start from this:

SELECT Teacher, Rating, Count(*) AS Ct
FROM YourTable
GROUP BY Teacher, Rating

Now, to determine the number of ratings for each teacher, almost the same
thing:

SELECT Teacher, Count(*) AS Ratings
FROM YourTable
GROUP BY Teacher

You divide the count of each rating by the total number of ratings.

You can save these two as separate queries, then base your end result on
them taken together, or you can put it all together in one query:

SELECT Teacher, Rating,
Count(*) / (SELECT COUNT(*)
FROM YourTable T1
WHERE T1.Teacher = T.Teacher)
* 100 AS Percnt
FROM YourTable T
GROUP BY Teacher, Rating

The above is a "correlated subquery" solution. To understand it you might
also need to look up "aliasing".

Tom Ellison