CODEWITHSUNDEEP
datetimemysql
halocursed
halocursed

Reputation: 2485

select rows from a table with date in the region- 90days ago and now.?

SELECT gameratingstblx245v.gameid,avg( gameratingstblx245v.rating ) as avgrating, count(gameratingstblx245v.rating) as count,gamedata.name ,gamedata.gameinfo
FROM gameratingstblx245v 
LEFT JOIN gamedata ON gamedata.id = gameratingstblx245v.game_id 
WHERE gameratingstblx245v.game_id=gameratingstblx245v.game_id 
GROUP BY gameid 
ORDER BY avg( gameratingstblx245v.rating ) DESC LIMIT 0,8

Table gameratingstblx245v - gameid, rating
Rable gamedata - id, gameinfo, name, releasedate

This is the query I am currently using to extract data from two tables gamedata and gameratingstblx245v.... What I am doing here is taking the avg. of all the ratings from table gameratingstblx245v in descending order of their avg. rating and I am also extracting the related info corresponding to the selected gameid's from table gamedata...

Now what I want to extract is the top avg. ratings from game_ratingstblx245v but for the games whose field releasedate from table gamedata is in the last 90 days... Help would be appreciated..Thanks

Upvotes: 2

Views: 1743

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

Here's how I'd design that query:

SELECT d.id, d.name, d.gameinfo,
  AVG(r.rating) AS avgrating, COUNT(r.rating) AS count
FROM gamedata d
LEFT JOIN gameratingstblx245v r ON (d.id = r.game_id)
WHERE d.releasedate BETWEEN NOW() - INTERVAL 90 DAY AND NOW()
GROUP BY d.id 
ORDER BY avgrating DESC LIMIT 0,8;

Upvotes: 6

Related Questions