Select rows from table with date in region - 90 days 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 strong> - id , gameinfo, name, issued
This is the query I am currently using to fetch data from two tables gamedata and gameratingstblx245v .... What I am doing here is taking avg. of all ratings from the gameratingstblx245v table in descending order of their average. rating and I also fetch the relevant information corresponding to the selected player from the gamedata table ...
Now what I want to extract is the top avg. ratings from game_ratingstblx245v, but for games whose field is released from the gamedata table in the last 90 days ... Help would be appreciated. Thank.
+2
source to share
1 answer
This is how I would query this request:
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;
+6
source to share