dimanche 12 juin 2016

Finding percentage of total for each item

I have a MYSQL database that tracks what students have gotten right and wrong on tests. It has columns Student, questionID, and trakStatus (which records Right/Wrong). What I would like to compile is a list by questionID of what percentage students have gotten correct for each one.

Currently, when I run my SQL line, it spits out a list of questionIDs but all with the same percentage calculation, the percentage for the last question. The output will look like this:

**QuestionID**   **Percentage**
1001              .2459
1002              .2459
1003              .2459
etc.

What I would like instead is a distinct percentage calc for each questionID. Here is the SQL I am using currently:

SELECT questionID, 
(SELECT COUNT( * ) 
FROM  storedQuestions 
WHERE trakStatus =  'Wrong') / 
(SELECT COUNT( * ) 
FROM  storedQuestions 
WHERE (
trakStatus =  'Wrong'
OR trakStatus =  'Right'
) ) AS Percentage
FROM storedQuestions
GROUP BY questionID

I can't figure out where I'm going wrong. This seems like it should work. Any advice would be MUCH appreciated. Thanks!

Aucun commentaire:

Enregistrer un commentaire