SQL query help needed

I have 3 simple tables

users:

ID

Code (markup):

wallpapers:

ID, user_id

Code (markup):

votes:

ID, wallpaper_id, vote_value

Code (markup):

People vote a wallpaper and I want my sql query to grab the top10 users.
My sql query looks like this:

SELECT users.ID, ROUND( SUM( votes.vote_value ) / COUNT( votes.ID ) ) AS score
FROM users
INNER JOIN wallpapers ON ( wallpapers.user_id = users.ID )
INNER JOIN votes ON ( wallpapers.ID = votes.wallpaper_id )
GROUP BY users.ID
ORDER BY score DESC

Code (markup):

If I have 3 wallpapers submitted and each one of them received votes, the query won't bring this result:

user_id has 3 votes

but instead it lists each one of them:
user_id has 1 vote
user_id has 1 vote
user_id has 1 vote

It needs a good relation between those 3 tables. I'm stuck, help please

 

Hello,

You can try the AVG function, I hope the below script may help you.

SELECT
users.ID, --ROUND( SUM(votes.vote_value ) / COUNT(votes.ID) ) AS score
avg(vote_value) score
FROM dbo.users
INNER JOIN wallpapers ON ( wallpapers.userid = users.ID )
INNER JOIN votes ON ( wallpapers.ID = votes.wallpaperid )
GROUP BY users.ID
ORDER BY score DESC

Eralper
http://www.kodyaz.com

 

I am not sure you need the third table called Votes. You can have the vote_value column in the second table wallpapers itself.

Then the SQL statement will be:

SELECT users.ID, avg(vote_value) as score
FROM users
INNER JOIN wallpapers ON ( wallpapers.user_id = users.ID )
GROUP BY users.ID
ORDER BY score DESC

cheers,
jay

 

Does the following work:

SELECT users.ID, AVG(votes.vote_value) as score
FROM users, wallpapers, votes
WHERE users.ID = wallpapers.user_ID
AND wallpapers.ID = votes.wallpaper_ID
GROUP BY users.ID
ORDER BY score

Like the above said, you don't need the votes table unless you want to keep track of individual votes (for preventing multiples votes from one user etc). You just need to add score, and num_votes to the wallpapers table. Then to update the score:

UPDATE wallpapers SET score = (score + @new_vote_value) / num_votes, num_votes = num_votes + 1 WHERE ID = @wallpaper_id

 

Hi lephron,

I do not think having score as a column and updating it is a good idea. For that matter, any computed value need not be a column unless you are using a computer with very low processing power.

cheers,
jay