CODEWITHSUNDEEP
mysqlsql
TomW
TomW

Reputation: 21

How can I get a sum from two different columns in a MySQL table

I have the following columns in a table called "match_scores":

player_1, player_2, player_1_points, player_2_points
tom, mike, 110, 40
tom, joe, 115, 30
mike, tom, 120, 30

I would like the result to be:

tom has 255 points
mike has 160 points
joe has 30 points

Upvotes: 1

Views: 105

Answers (3)

Tarun
Tarun

Reputation: 154

This should work..

select player, sum(points) as points
from (select player_1 as player, player_1_points as points 
      from match_scores 
      union all
      select player_2 as player, player_2_points as points 
      from match_scores) as a
group by player;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can "unpivot" the data and then re-aggregate. In SQL, this looks like:

select player, sum(points) as points
from ((select player_1 as player, player_1_points as points from match_scores ms1) union all
      (select player_2 as player, player_2_points as points from match_scores ms2) 
     ) t
group by player;

This returns the data as a result set with two columns. You can convert to a string in SQL, but I really recommend that you do that at the application layer.

Upvotes: 4

TomW
TomW

Reputation: 21

This is the query that resolved the issue:

SELECT DISTINCT (player), sum(points)
FROM (
       SELECT player_1 AS player, player_1_points AS points FROM match_scores
       UNION ALL 
       SELECT player_2 AS player, player_2_points AS points FROM match_scores
     ) AS t
GROUP BY player

so the placement of alias as you notice is right at the end, and only one. For some reason the placement of aliases at end of each select still caused the error

Upvotes: 1

Related Questions