MySQL: select when count = sum & group by

Question

I need to select user_id & quiz_id, for users which their count of questions in their quiz = sum of correct, this mean they answer 100% correct

answers table:

quiz_id      question_id      user_id      answer_id      correct
1                1               1             1            1
1                2               1             6            0
1                3               1             9            1
2                1               2             1            1
2                2               2             5            1
3                4               1            17            1
3                5               1            21            1
3                6               1            25            1
4                1               3             1            1
5                4               4            18            0
6                1               5             1            1
6                2               5             5            1
7                1               3             2            0
7                2               3             7            0

ex 1: user 1 took "quiz_id" = 1

count of questions in "quiz_id = 1" = 3

sum of correct = 2

so it's not 100%

user_id = 1 in quiz_id = 1 => will not selected

but user_id = 1 will be selected with quiz_id = 3 cause he got 100%

expected results:

quiz_id     user_id
  2            2
  3            1
  4            3
  6            5

notes:

quiz could be taken with different users with different number of questions
quiz_id, user_id unique together (user can not take same quiz twice)

thanks,

Matteo Tassinari · Accepted Answer · 2015-06-11 12:51:03Z

2

You should use an aggregate query with HAVING clause:

SELECT quiz_id, user_id
FROM quiz_answer -- or whatever the name is
GROUP BY quiz_id, user_id
HAVING COUNT(question_id) = SUM(correct)

here you must use HAVING instead of WHERE because

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot

as specified in the docs.

answered Jun 11, 2015 at 12:51

Matteo Tassinari

18.6k8 gold badges65 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: select when count = sum & group by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related