I am running analyses on a quiz and am trying to manipulate querys in order to produce bar charts. I am trying to display a graph which shows the percentages of candidates passing a particular quiz. The query I am trying to run is as follows:

SELECT QuizName, (100*(COUNT(PassFail))/(COUNT(XXXXXX))) AS 'Total'
FROM Resultsets
WHERE PassFail ='PASS'
GROUP BY QuizName

Where XXXXX has been a number of things listed below, all which have failed.

1) I have tried to use the line (100*(COUNT(PassFail),(WHERE PassFail= PASS)) / (COUNT (PassFail)))
2) I have also tried creating a routine which counts the number of times a Quiz has been used and than using CALL in the place of COUNT(XXXX), this didnt work.

I am not sure what to do? The quizzes have been attempted a different number of times so I cant just insert a number. Any help would be greatly appreciated!! Plus if i have emitted any necessary information please let me know and I will include it.

Many thanks,

Will

Can you show your table structure and some sample data?

I have a large number of data under the following headers. An example of the data under these tables is shown also.

Candidate = Candidate A
Quiz Name = Quiz A
Overall Score = 10
Total possible score = 20
Percentage score = 50
Percentage pass = 80
Pass Fail = Fail
ResultsetID = 1
Total Time = 300

Ideally I would like a query that would enable me to plot a graph describing, for example, how many candidates achieved a score over 50%, where the 50% can be easily changed. This is unfortunately beyond me so what I have done is created a query that uses the already inputted pass/fail mark of 80% used count to find out how many people have passed the exam. What I am missing is the total number of times the quiz has been taken so I can give a percentage of candidates passing.
If you could provide me with any information on either the first query, which is beyond me, or the query I have nearly manage to get work I would be very very grateful!

<?php 
$pass="80"; //this you can pass from html form to the query

$query="select sum(case when percentagescore>='$pass' then 1 else 0 end ) / count(*)*100 pass_percent,
               sum(case when percentagescore<'$pass' then 1 else 0 end ) / count(*)*100 fail_percent
        from tablename";

?>

Thank you very much that is an excellent solution!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.