Calculated field (Percent)

1

I am trying to get the query to output the percentage by dividing the Correct SUM field with the count field and it is returning an unknown column name error:

SELECT Q.*, (Correct/total) AS percent FROM (SELECT Q.firstname, SUM(CASE WHEN Q.response != Q.answer THEN 1 END) AS Incorrect, SUM(CASE WHEN Q.response = Q.answer THEN 1 END) AS Correct, COUNT(Q.Quiz_question_id) as total FROM (SELECT User_Accounts_.user_id, User_Accounts_.firstname, User_Accounts_.lastname, Quiz.quiz_id AS Quiz_id2, Quiz.quiz_title, Quiz_Assign.quiz_assign_id, Quiz_Assign.quiz_id AS Quiz_id1, Quiz_Questions.quiz_question_id AS QQID, Quiz_Questions.quiz_id, Quiz_Questions.question, Quiz_Questions.option1, Quiz_Questions.option2, Quiz_Questions.option3, Quiz_Assign_Student_Question.quiz_question_id, Quiz_Assign_Student_Question.response, Quiz_Questions.answer, Class_List.Class_List_id AS CLID, Quiz_Assign.class_list_id FROM User_Accounts_, Class_List, Quiz, Quiz_Assign, Quiz_Questions, Quiz_Assign_Student_Question WHERE (Quiz.quiz_id=Quiz_Assign.quiz_id) AND (Quiz_Questions.quiz_id=Quiz.quiz_id) AND (Quiz_Questions.quiz_question_id=Quiz_Assign_Student_Question.quiz_question_id) AND (Quiz_Assign_Student_Question.quiz_assign_id=Quiz_Assign.quiz_assign_id) AND (Class_List.Class_List_id=Quiz_Assign.class_list_id) AND (Quiz_Assign.quiz_id=16) AND (Class_List.class_id=29) AND (User_Accounts_.user_id=Class_List.user_id)) AS Q;

2

I tried parsing your query and found a couple of problems. One is using "percent" as a field name when it is a reserved word in T-SQL. Another is with the closing of the derived table. Here's the updated SQL with these problems corrected:

SELECT Q.*,
(Correct/total) AS percent1
FROM (
	SELECT Q.firstname, SUM(CASE WHEN Q.response != Q.answer THEN 1 END) AS Incorrect,
	SUM(CASE WHEN Q.response = Q.answer THEN 1 END) AS Correct, 
	COUNT(Q.Quiz_question_id) as total
	FROM (
		SELECT User_Accounts_.user_id, User_Accounts_.firstname, User_Accounts_.lastname, Quiz.quiz_id AS Quiz_id2, Quiz.quiz_title, Quiz_Assign.quiz_assign_id, Quiz_Assign.quiz_id AS Quiz_id1,
		Quiz_Questions.quiz_question_id AS QQID, Quiz_Questions.quiz_id, Quiz_Questions.question, Quiz_Questions.option1, Quiz_Questions.option2, Quiz_Questions.option3, 
		Quiz_Assign_Student_Question.quiz_question_id, Quiz_Assign_Student_Question.response, Quiz_Questions.answer, Class_List.Class_List_id AS CLID, Quiz_Assign.class_list_id
		FROM User_Accounts_, Class_List, Quiz, Quiz_Assign, Quiz_Questions, Quiz_Assign_Student_Question
		WHERE (Quiz.quiz_id=Quiz_Assign.quiz_id) AND (Quiz_Questions.quiz_id=Quiz.quiz_id) AND (Quiz_Questions.quiz_question_id=Quiz_Assign_Student_Question.quiz_question_id) AND
		(Quiz_Assign_Student_Question.quiz_assign_id=Quiz_Assign.quiz_assign_id) AND (Class_List.Class_List_id=Quiz_Assign.class_list_id) AND (Quiz_Assign.quiz_id=16) AND (Class_List.class_id=29) AND
		(User_Accounts_.user_id=Class_List.user_id)
	) AS Q
) Q
3

I see several issues with this query - you are selecting from a derived table which is selecting from a derived table, using '*' in the outer query, using the old-style join syntax, no table aliases, no grouping as well as the missing close of one of those derived tables.

This should get you the desired results:

SELECT ua.firstname
     , SUM(CASE WHEN qasq.response <> qq.answer THEN 1 END) AS Incorrect
     , SUM(CASE WHEN qasq.response = qq.answer THEN 1 END) AS Correct
     , COUNT(qq.quiz_question_id) AS Total
     , SUM(CASE WHEN qasq.response = qq.answer THEN 1 END) / COUNT(qq.quiz_question_id) AS [percent]
  FROM User_Accounts_                    AS ua
 INNER JOIN Class_List                   AS cl ON cl.user_id = ua.user_id
 INNER JOIN Quiz_Assign                  AS qa ON qa.class_list_id = cl.class_list_id
 INNER JOIN Quiz_Assign_Student_Question AS qasq ON qasq.quiz_assign_id = qa.quiz_assign_id
 INNER JOIN Quiz_Questions               AS qq ON qq.quiz_question_id = qasq.quiz_question_id
 WHERE qa.quiz_id = 16
   AND cl.class_id = 29
 GROUP BY
       ua.firstname;

With that said - you probably don't want to group on the first name. You probably should group on the user ID and return the full name. The full name could be done as: CONCAT_WS(' ', ua.firstname, ua.lastname).

4

Thank you! I really learnt from this!

5

Thank you! Lots taken from this!

How do I ORDER BY on percent1 in DESC as Group by and Order by cannot be used together?

6

You can use both GROUP BY and ORDER BY together - not sure why you think you cannot do that. Since the ORDER BY is processed after the SELECT you can also use the column alias in the ORDER BY so all you need to do is add:

 ORDER BY
       [Percent] DESC