Welcome to WebmasterWorld Guest from 18.104.22.168
Forum Moderators: open
Ive been trying but just cant figure out a proper query to do what I need. I have posted an example of what I have so far below and Ill try to explain what Im doing as well.
I have two tables:
1. Ratings -> Structure: id (INT), rating_id (INT), rating_num (INT), IP (VARCHAR) Where rating_id is the game the rating is for's ID.
2. Games -> Structure: id (INT), catid (INT) (Simplified as this is all we need where catid is the id of the category the game belongs to).
What I need to do is order the games in order of their rating. Where a 5/5 rating would come first and 0/5 last.
The query I tried to use to accomplish this (warning its VERY messy and probably far off from what I need) is:
SELECT games.id, SUM(ratings.rating_num) as rsum, COUNT(ratings.id) as rnum, (SUM(ratings.rating_num)/COUNT(ratings.id)) as rating FROM games, ratings WHERE ratings.rating_id = games.id ORDER BY rating
For some reason thats probably obvious I'm getting only a single result where the query is summing up the ratings of ALL of the games and spitting out only one result where it should be summing up for each individual game and giving me multiple results.
Thanks in advance!