CODEWITHSUNDEEP
mysqlsqljoinaverage
Ludvig Alexander Brüchmann
Ludvig Alexander Brüchmann

Reputation: 25

SQL JOIN two tables with AVG

I am trying to join two tables:

songs
id | song | artist
---|------|-------
1  | foo  | bar
2  | fuu  | bor
3  | fyy  | bir

score
id | score
---|------
1  | 2
2  | 4
3  | 8
2  | 6
3  | 2

using this SQL command:

SELECT songs.id, songs.song, songs.artist, score.score FROM songs LEFT JOIN score ON score.id=songs.id ORDER BY songs.id, score DESC

What I get back is duplicates of the same song with multiple scores, I would like the score to be averaged.

result
id | song | artist | score
---|------|--------|-------
1  | foo  | bar    | 2
2  | fuu  | bor    | 4
2  | fuu  | bor    | 6
3  | fyy  | bir    | 8
3  | fyy  | bir    | 2

I tried that using:

SELECT songs.id, songs.song, songs.artist, ROUND(AVG(score.score),1) AS 'score' FROM songs INNER JOIN score ON score.id=songs.id ORDER BY score DESC

But that averages all scores, not just the score of each individual song

result
id | song | artist | score
---|------|--------|-------
1  | foo  | bar    | 4.4

Upvotes: 1

Views: 5608

Answers (4)

Martin Hučko
Martin Hučko

Reputation: 791

You need to GROUP BY and join data to left (JOIN LEFT)

try this:

SELECT
  songs.id,
  songs.song,
  songs.artist,
  ROUND(AVG(score.score), 1) AS 'score'
FROM songs
LEFT JOIN score ON score.id = songs.id
GROUP BY songs.id
ORDER BY score DESC

Upvotes: 0

yatin parab
yatin parab

Reputation: 174

Use this Select a.id,a.song,a.artist,avg(b.score) as score from songs a inner join score b on a.id =b.id Group by a.id,a.artist,a.song

Upvotes: 0

John Pasquet
John Pasquet

Reputation: 1842

You need to GROUP BY all the fields you want to retain:

SELECT songs.id, songs.song, songs.artist, 
    AVG(score.score * 1.0) AS AvgScore
FROM songs 
    LEFT JOIN score 
        ON score.id=songs.id 
GROUP BY songs.id, songs.song, songs.artist
ORDER BY songs.id, score DESC

Alternatively, you could just do this:

SELECT songs.id, songs.song, songs.artist, 
    (SELECT AVG(Score) FROM score WHERE score.id = songs.id) AS AvgScore)
FROM songs

Upvotes: 8

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

use "group by" songs.id

    SELECT songs.id, songs.song, songs.artist, 
   ROUND(AVG(score.score),1) AS 'score' FROM songs 
    INNER JOIN score ON score.id=songs.id  
group by songs.id ORDER BY score DESC

Upvotes: 0

Related Questions