Count and where of count

[crashwave]

I tutor at the university and we want to keep track of student grades for certain classes to see what classes need more attention.

We have created the DB and have queries running successfully except this one that has me stumped

We have (example)

Year  Semester  Course  Section  Grade

2010  Spring  1001            02          A

2010  Spring  1001            02          F

2010  Spring  1001            01          C

2010  Spring  1001            02          A

2010  Spring  1002            03          B

2010  Spring  1001            02          A

2010  Spring  1001            04          W

....

 

We want to calculate the % students who received an A, B or C in each CLASS (Total ABC/Total Students)

We have it calculating the total students and the total passing, but I cannot to give me both the total student AND the total ABC with the same statement

 

 

What we want is something like

Semester  Class      Total    Pass    %

201003    1001 01      39      31      31/39

 

Any Help appreciated

Thanks

 

[Barand]

Use a subquery, for example:

SELECT a.semester, a.class, SUM(CASE WHEN grade IN ('A'.'B','C') THEN 1 ELSE 0 END) as pass, x.total
FROM mytable a
INNER JOIN (
  SELECT semester, class, COUNT(*) as total
  FROM mytable
  GROUP BY semester, class
) as x USING (semester, class)
GROUP BY a.semester, a.class