How do I MELT a SQL table?

user_8914900 0
Viewed 8192

I have the following SQL table, lets call it teams_score which shows teams of 2 players for a game and their score. I need to observe the score for each player each time they appear on a team as either Player1 of Player2. I think this is like melting. 

Player1,         Player2,          TeamScore
"Johnny Onspot", "Pat Magroin",     95
"Pepe Roni",     "Cole Kutz",       78
"Ben Dover",     "Ibin Yerkinoff",  76
"Johnny Onspot", "Frumunda Mabalz", 69
"Sal Lami",      "Cole Kutz",       65
"Pat Magroin",   "Frumunda Mabalz", 63

I want to find the top 3 players with the highest average score.

For example, "Pat Magroin" appeared on 2 teams with scores 95 and 63 for and average score of 79

So I need to display the table "player_score" as shown below and then aggregate by the average score. HOW DO I GET THE TABLE player_score SHOWN BELOW?

Player,            Score
"Ben Dover",       76
"Cole Kutz",       78
"Cole Kutz",       65
"Frumunda Mabalz", 69
"Frumunda Mabalz", 63
"Ibin Yerkinoff",  76
"Johnny Onspot",   95
"Johnny Onspot",   69
"Pat Magroin",     95
"Pat Magroin",     63
"Pepe Roni",       78
"Sal Lami",        65

Once I have player_score, I should be able to run the query (shown below) to get the table result_table as shown below.

SELECT Player, AVG(Score) AS Avg_Score FROM player_score
GROUP BY Player
ORDER BY Avg_Score DESC
LIMIT 3;

Player,            Avg_Score
"Johnny Onspot",   82
"Pat Magroin",     79
"Pepe Roni",       78
melt sqlite
3 Answers
SELECT *
FROM (
    SELECT Player1 AS Player, TeamScore as Score
    FROM teams_score

    UNION ALL

    SELECT Player2 AS Player, TeamScore as Score
    FROM teams_score
)

Should give

Player                  Score
"Johnny Onspot",        95
"Pat Magroin",          95
"Pepe Roni",            78
"Cole Kutz",            78
"Ben Dover",            76
"Ibin Yerkinoff",       76
"Johnny Onspot",        69
"Frumunda Mabalz",      69
"Sal Lami",             65
"Cole Kutz",            65
"Pat Magroin",          63
"Frumunda Mabalz",      63

Note the use of UNION ALL (over just using UNION) to ensure the inclusion of duplicate rows. This lets the average be computed properly.

From here, you should be able to do what you wanted

WITH
    ...above code
AS player_score
SELECT Player, AVG(Score) AS Avg_Score
FROM player_score
GROUP BY Player
ORDER BY Avg_Score DESC
LIMIT 3;
user_111412710

You could use UNION ALL:

SELECT Player, AVG(score) AS avg_score
FROM(SELECT Player1 AS player, Score FROM player_score
     UNION ALL SELECT Player2, Score FROM player_score) sub
GROUP BY Player
ORDER BY avg_Score DESC
LIMIT 3;
user_50708790 
CREATE TABLE player_score AS SELECT Player1 AS Player, TeamScore AS Score FROM(
SELECT Player1, TeamScore
FROM teams_score
UNION ALL
SELECT Player2, TeamScore
FROM teams_score);

-- Delete duplicate rows where player and score are the same in both rows
DELETE FROM player_score WHERE rowid NOT IN 
(SELECT min(rowid) FROM player_score GROUP BY Player, Score);
user_89149000
Related