List of players joined with average ratings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

List of players joined with average ratings

Author

Topic

sq2
Starting Member

11 Posts

Posted - 2012-09-04 : 07:48:11

Hi,

Been a while since I've been here, sort most sql issues out myself, but not this one!

I have two tables: player, player_ratings

players: player_id, player_name
the players table is self explanatory

player_ratings: rating_id, player_id, rater_id, stars
each player can have multiple player ratings.

I'm trying to get all the players with 'c' in their name, and at the same time each players average number of stars (most players have no ratings, ie stars)

I have tried many queries, but with no luck. Here are two attempts:

SELECT players.player_id, players.player_name, player_ratings.player_id, AVG(player_ratings.stars)
FROM players AS players
JOIN player_ratings AS player_ratings ON players.player_id = player_ratings.player_id
WHERE players.player_name LIKE '%c%'
ORDER BY player_name
ASC LIMIT 10

or

SELECT player_ratings.*, positions.* FROM (
SELECT player_id, player_name FROM players
WHERE player_name LIKE '%c%'
ORDER by player_name ASC LIMIT 40
) AS positions INNER JOIN (
SELECT player_id AS player_id_rating, AVG(stars) as stars, COUNT(*) as count FROM player_ratings
) AS player_ratings ON positions.player_id = player_ratings.player_id_rating

which seems to be averaging all stars, which is to be expected I guess.

As always, I can achieve this in multiple queries, but clunky.

Thanks for any ideas.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 08:14:49

If I understood your requirement correctly, the first query is almost right. See if the following would give you what you are looking for:

SELECT TOP (10) players.player_id,
       players.player_name,
       --player_ratings.player_id,
       AVG(player_ratings.stars)
FROM   players              AS players
       JOIN player_ratings  AS player_ratings
            ON  players.player_id = player_ratings.player_id
WHERE  players.player_name LIKE '%c%'
GROUP BY 
       players.player_id,
       players.player_name
       -- ,player_ratings.player_id
ORDER BY
       player_name
       --ASC LIMIT 10

Mike Jackson
Starting Member

37 Posts

Posted - 2012-09-04 : 08:16:52

SELECT players.player_id, players.player_name,
count(player_ratings.player_id), AVG(player_ratings.stars)
FROM players AS players
JOIN player_ratings AS player_ratings
ON players.player_id = player_ratings.player_id
WHERE players.player_name LIKE '%c%'
group by players.player_id, players.player_name

sq2
Starting Member

11 Posts

Posted - 2012-09-04 : 08:22:54

Ignoring the where clause for the moment, the TOP(10) is causing an error (I'm using MYSQL, this could be the issue).

However, the following seems to work, but it omits those who have had no rating yet:

SELECT players.player_id, players.player_name, AVG( player_ratings.stars ) 
FROM players AS players
INNER JOIN player_ratings AS player_ratings ON players.player_id = player_ratings.player_id
GROUP BY players.player_id, players.player_name
ORDER BY player_name
LIMIT 0 , 30

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 08:37:37

Use a LEFT JOIN instead of INNER JOIN.

HOWEVER: I am not familiar with MySQL, and most people on this forum are not experts either. This forum is all about Microsoft SQL Server. You may get better and faster responses at a MySQL specific forum or a generalized db forum (dbforums.com).

sq2
Starting Member

11 Posts

Posted - 2012-09-04 : 08:42:08

Thanks for your help... the left join seems to do the trick!

quote:
Originally posted by sunitabeck

Use a LEFT JOIN instead of INNER JOIN.

HOWEVER: I am not familiar with MySQL, and most people on this forum are not experts either. This forum is all about Microsoft SQL Server. You may get better and faster responses at a MySQL specific forum or a generalized db forum (dbforums.com).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources