CODEWITHSUNDEEP
mysql
karatechops
karatechops

Reputation: 43

mySQL query comparing rows and columns

I have a database of user answers from a quiz which contains 8 questions. Q1 - Q8 all are their own columns, I'd like to compare all the rows and get a number back for everyone who answered the same for at least 5 questions.

enter image description here

so here, rows 5 and 6 would count as 2. Basically I'm trying to get a number for everyone who answered at least 5 questions the same. Is this possible with a mySQL query?

EDIT:

enter image description here

Here the user enters D B D A B C D B, matching with 2 similarly answered quizzes. The query here would return a count of 2.

Upvotes: 4

Views: 222

Answers (2)

Matthew Dally
Matthew Dally

Reputation: 412

If we test using just your single line of D B D A B C D B we can use the following example:

SELECT * FROM `answers` WHERE  ((CASE WHEN q1 = 'D' THEN 1 ELSE 0 END) +     
(CASE WHEN q2 = 'B' THEN 1 ELSE 0 END) +     
(CASE WHEN q3 = 'D' THEN 1 ELSE 0 END) +         
(CASE WHEN q4 = 'A' THEN 1 ELSE 0 END) +     
(CASE WHEN q5 = 'B' THEN 1 ELSE 0 END) +     
(CASE WHEN q6 = 'C' THEN 1 ELSE 0 END) +     
(CASE WHEN q7 = 'D' THEN 1 ELSE 0 END) +         
(CASE WHEN q8 = 'B' THEN 1 ELSE 0 END)) >= 5;

However, if we then want to go a step further and test each answer against the other answers in the table we can use the following statement:

SELECT *, (SELECT COUNT(answer_sub.idanswers) FROM `answers` answer_sub     
WHERE ((CASE WHEN answer_sub.q1 = a.q1 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q2 = a.q2 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q3 = a.q3 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q4 = a.q4 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q5 = a.q5 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q6 = a.q6 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q7 = a.q7 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q8 = a.q8 THEN 1 ELSE 0 END)) >= 5     
AND answer_sub.idanswers <> a.idanswers) as matching    
FROM `answers` a    
WHERE (SELECT COUNT(answer_sub.idanswers) FROM `answers` answer_sub     
WHERE ((CASE WHEN answer_sub.q1 = a.q1 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q2 = a.q2 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q3 = a.q3 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q4 = a.q4 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q5 = a.q5 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q6 = a.q6 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q7 = a.q7 THEN 1 ELSE 0 END) +     
(CASE WHEN answer_sub.q8 = a.q8 THEN 1 ELSE 0 END)) >= 5     
AND answer_sub.idanswers <> a.idanswers) > 0

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

Because FALSE is 0 and TRUE is 1 in MySQL:

SELECT COUNT(*)
FROM quiz
WHERE ( (q1=@q1) + (q2=@q2) + (q3=@q3) + (q4=@q4)
      + (q5=@q5) + (q6=@q6) + (q7=@q7) + (q8=@q8) 
      ) >= 5

Upvotes: 0

Related Questions