Calculating Percent Passed on Test Scores
516805Mar 27 2008 — edited Mar 27 2008I am trying to calculate percentage of students passing an exam. I have the SQL query to find number of students taking a particular exam, and another query to find the number of students passing the exam. But to calculate the percentage I need to obtain both numbers in a single SQL query so that I can divide to find the percent. The problem is that the way the tables are structured, I can't find a way to get those counts in a single query and show the results for each of the multiple times that the exam is offered. Below is the SQL to get counts taking an exam:
SELECT i.NAME AS institution, ci.coursenumber,
COUNT(*) AS NumberStudents
FROM emtp.EDU_EXAMRESULT res
INNER JOIN emtp.edu_examroster er
ON res.examrosterid = er.examrosterid
INNER JOIN emtp.EDU_COURSEEXAMROSTER cer
ON er.examrosterid = cer.examrosterid
INNER JOIN emtp.EDU_COURSEROSTER cr
ON cer.courserosterid = cr.courserosterid
INNER JOIN emtp.EDU_COURSEINSTANCE ci
ON cr.courseinstanceid = ci.courseinstanceid
INNER JOIN emtp.EDU_INSTITUTION i
ON ci.institutionid = i.institutionid
INNER JOIN emtp.EDU_EXAM e
ON er.examid = e.examid
INNER JOIN emtp.EDU_COURSE c
ON ci.courseid = c.courseid
WHERE e.examdate >= '01-Jan-07'
AND e.examdate <= '31-Dec-07'
AND e.examstatusluid IN
(SELECT examstatusluid FROM emtp.edu_examstatuslu
WHERE LABEL = 'Complete')
AND c.coursename LIKE 'Initial%'
GROUP BY i.NAME, ci.coursenumber
ORDER BY i.NAME, ci.coursenumber;
which gives me output as below:
INSTITUTION COURSENUMBER NUMBERSTUDENTS
========== ============== ===============
xxx College 12345 14
yyy College 67890 41
To get the number of student who passed the exam, I use the same SQL query as above with the following extra AND to the WHERE clause:
AND res.EXAMRESULTLUID IN
(SELECT examresultluid FROM emtp.edu_examresultlu
WHERE LABEL = 'Pass')
Any ideas how to combine these two SQL queries into one, so that I have both number of students and number who passed the exam so that I can calculate the percentage who passed a particular exam?