Welcome to WebmasterWorld Guest from 107.20.104.161

Forum Moderators: open

Message Too Old, No Replies

Need Help With A Query

     

trigoon

2:17 pm on Dec 23, 2009 (gmt 0)

5+ Year Member



Hello,

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!

whoisgregg

11:45 pm on Jan 4, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Perhaps a GROUP BY is in order? Something like this may do the trick:

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
[b]GROUP BY games.id[/b]
ORDER BY rating
 

Featured Threads

Hot Threads This Week

Hot Threads This Month