SQL Compute Average of Two Fields

I have a database table which has three relevant fields, firstly a user id, then one is a number of submitted nodes, the other is a sum of the "score" for each node.

I need to be able to run a query which will return the users with the highest average scores, b开发者_开发知识库ut my SQL-fu is not where it could be.

Cheers!

Here's what you need. It's important to cast your int data types to something that handles decimals, or you'll get inaccurate averages:

SELECT *, CAST(total_score AS FLOAT) / CAST(total_nodes AS FLOAT) AS average_score
FROM yourtable
ORDER BY CAST(total_score AS FLOAT) / CAST(total_nodes AS FLOAT) DESC

Good luck!

-Michael

Are you looking for

-- Multiplied by 1.0 to convert it to double
Select top 100 user_id, (1.0 * total_score/total_node) as AvgScore
From   YourTable
Order By (total_score/total_node) asc

SELECT AVG(user.score) AS avg_score 
FROM user 
GROUP BY user.id 
ORDER BY avg_score DESC 

Try this:

select userid,max(avgscore) as maxscore
from 
(select userid,avg(score) as avgscore from usertable group by userid) mytab

Easer would be

SELECT AVG(user.score1 + user.score2)/2 as average, userid FROM usertable
GROUP BY userid
ORDER BY average

Mind your datatypes. If the scores are integers, you might first want to cast the average to decimals.