CODEWITHSUNDEEP
phpmysqlsqlmath
Gricey
Gricey

Reputation: 1441

SQL query for averaging and rounding

I need a query which will update a row in Games to have a value of the average of its recorded Ratings multiplied by the number of Plays it has had. This is what I have so far but it doesn't seem to be working:

UPDATE Games
SET PlaysRating = ROUND(AVG
                       (SELECT `Rating` FROM GameRatings WHERE GameID = '37') 
                     * (SELECT COUNT(*) FROM Plays WHERE GameID = '37')) 
WHERE ID = 37

Upvotes: 1

Views: 75

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658322

Could look like this:

UPDATE Games
SET    PlaysRating = round(
       (SELECT count(*)    FROM Plays       WHERE GameID = 37)
     * (SELECT avg(Rating) FROM GameRatings WHERE GameID = 37))
WHERE  ID = 37

BTW, assuming ID is a numeric type, I removed the quotes ''.

Upvotes: 3

Related Questions