Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I count the average number closest to 100?

Tags:

mysql

I have a MySQL table where I got a list of numbers, gathered from a game where users have to click a button to get close to 100.

I thought I'd use AVG(), but this won't work obviously, because when you have 0 and 200, it will look as if you scored a perfect 100.

What I need is for every round (there are 10) count how far away from 100 the number is and count those.

Example:
User A: 98 + 102 + 102 = 6 (something) Loser!
User B: 95 + 100 + 100 = 5 (something) Winner!

What I had, but is obviously flawed:

SELECT user_id, AVG(score) AS Average, (100-AVG(score)) AS Difference, 
DATE(playtime) AS Playdate 
FROM playtable
GROUP BY user_id, DATE(playtime) 
ORDER BY Playdate DESC, user_id
like image 873
Gerben Jacobs Avatar asked Dec 19 '12 23:12

Gerben Jacobs


1 Answers

This should do it

SELECT user_id, SUM( ABS(100-score) ) as cumulative_error,
  DATE(playtime) AS Playdate 
FROM playtable
GROUP BY user_id, DATE(playtime) 
ORDER BY Playdate DESC, user_id

cumulative_error will contain the accumulated difference values

like image 181
Damp Avatar answered Sep 28 '22 09:09

Damp