Getting average in MySQL

Question

I have a question using MySQL. I have 2 tables, one with names of games and one with different ratings for the games. I want to be able to search for a game by name and see the average ratings for each game. So far I kind of managed to get the average rating, but I will only see the rating of 1 game instead of multiple entries. Here's my query so far:

SELECT games.name, CAST(AVG(reviews.rating) as decimal(6, 1)) AS average_rating
    FROM games
    JOIN reviews
    ON games.id = reviews.game
    WHERE games.name LIKE '%spider%'
    AND type = '7'
    AND rating != 0

I hope some of you smart people can help me out with this! Thanks, Robert

citizen404 · Accepted Answer · 2014-04-07 00:20:26Z

2

You have to use a GROUP BY clause on the proper field to get the average for each group, otherwise it will calculate the average of all rows. I guess it's games.id, but depends on your table schemata.

SELECT games.name, CAST(AVG(reviews.rating) as decimal(6, 1)) AS average_rating
FROM games
JOIN reviews
ON games.id = reviews.game
WHERE games.name LIKE '%spider%'
AND type = '7'
AND rating != 0
GROUP BY games.id;

Read more about so called aggregate functions

answered Apr 7, 2014 at 0:20

citizen404

1,5151 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zanerock · Accepted Answer · 2014-04-07 00:17:41Z

2

You have to use GROUP BY games.name to separate out the different names. The problem is that AVG is a summary function that collapses the results. The GROUP BY breaks the results out.

SELECT games.name, CAST(AVG(reviews.rating) as decimal(6, 1)) AS average_rating 
FROM games 
  JOIN reviews ON games.id = reviews.game 
WHERE games.name LIKE '%spider%' AND type = '7' AND rating != 0
GROUP BY games.name

answered Apr 7, 2014 at 0:17

zanerock

3,6823 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Getting average in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related