MySQL query problem

[roelj]

This is the query I'm running:

SELECT `user_id`, SUM(`is_correct`) / COUNT(`id`) AS `score` FROM `UserAnswers` 
WHERE `user_id` IN ( SELECT DISTINCT `user_id` FROM `UserAnswers` );

Which gives this result:

+---------+--------+
| user_id | score  |
+---------+--------+
|       1 | 0.7647 |
+---------+--------+

The subquery returns:

+---------+
| user_id |
+---------+
|       1 |
|       4 |
+---------+

So, why does the first query only return 1 row, while the subquery returns 2 rows?

Thanks for your time.

[andinowich]

Hi roelj,

I think the problem is that you use group-functions like "sum" and "count". Try the following query:

SELECT `user_id`, SUM(`is_correct`) / COUNT(`id`) AS `score` FROM `UserAnswers` GROUP BY `user_id`

regards
andinowich

[roelj]

That works! Great solution!

However, I don't understand how the SUM() and/or COUNT() function(s) mess it up.
The functions are used on a row result, so it should only affect one row..?

Thanks for the quick reply and solution. It's working correctly now.

[andinowich]

Hi!

The SUM and COUNT functions sum up and count all rows which are returned by the query if you don't use group by. Maybe the following link will help you:

http://dev.mysql.com/doc/refman/5.0/...functions.html

I'm new in this forum but I think you should mark the thread as solved?

regards
andinowich

[roelj]

OK, thanks for the solution and the explanation :-).