How to sum and divide in MySql
Alright, so I have a user table and would like to get the max value for the user with the highest amount of points divided by a score. Below is a rough idea of what I'm looking for:
SELECT MAX(SUM(points)/SUM(score)) FROM users
I'm not interested in adding up both columns and dividing, rather I'm interested in dividing the points and score for each user and retrieve the highest value out of the lot.
Maybe you could do this with a subquery:
Select max(points_over_Score)
from
(Select points/score AS points_over_score
from users);
And as thesunneversets mentioned in a comment, that can probably be shortened to
SELECT MAX(points/score) FROM users;
You're written description of what you're trying to do doesn't make it clear why your example has SUM in it, so I didn't use it.
Also, code questions like this one are more appropriate for stackoverflow.com. You can flag your own question to ask a moderator to migrate it.
FrustratedWithFormsDesigner's answer won't work if you need to sum up all of the points, then all of the scores, and then divide the two. That answer divides each point by the score, then returns the highest answer.
This should work:
SELECT MAX(sum_points / sum_score) from
(SELECT SUM(points) as sum_points, SUM(score) as sum_score FROM users)
BTW, this returns an "unusual" percentage. You probably want to divide score by points. For example, if you get 70 out of 100, that is 70%, or score (70) / points (100).
I'm not sure if this is due to the age of this post or version of MySQL at the time, but when I tried the above solutions the following error was thrown:
ERROR 1248 (42000) : Every derived table must have its own alias
I was able to resolve this using the following:
SELECT MAX(sum_points / sum_score) from (SELECT SUM(points) as sum_points, SUM(score) as sum_score FROM users) as max_value;
下一篇: 如何在MySql中进行求和和分割