SQL Query to pull data from multiple tables, plus maybe some basic calculations
I'm struggling to get my head around a query. I know how to do basic SQL but I'm a bit out of my depth with this one.
I'm trying to set up a query that returns a chart of highest ranked games. Users can rank a game out of 10. I want to select a list of the top 10 games and display them in a chart based on what their average rank is and also by the number of votes they have. So a game with an average rank of 8 and 20 votes will appear higher up the chart than a game with 10 votes and a average rank of 8.
If this can't be be done with pure SQL then I can always do some coding for the rest. At this stage its just about getting the data I need, and in the right format.
Any help would be much a开发者_JS百科ppreciated.
My table structure is as follows:
games
| id | title | platform | genre |
users
| id | email | username | password |
votes
| userid | gameid | vote |
The format of data I want to return is:
| title | platform | average rank | votes |
You could group by
on games, and the rest is pretty simple:
select games.title
, games.platform
, games.genre
, avg(votes.vote) as AvgRank
, count(*) as VoteCount
from games
join votes
on votes.gamesid = games.gameid
join users
on users.id = votes.userid
group by
games.title
, games.platform
, games.genre
order by
avg(votes.vote) desc
, count(*) desc
limit 10
This query uses limit 10
to get the first 10 rows. If you were using SQL Server, you'd use select top 10 ...
. Oracle uses where rownum < 11
.
精彩评论