homepage Welcome to WebmasterWorld Guest from 54.167.238.60
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Need Help With A Query
trigoon




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved