Select multiple sums with MySQL query and display them in separate columns

Question

Let's say I have a hypothetical table like so that records when some player in some game scores a point:

name   points
------------
bob     10
mike    03
mike    04
bob     06

How would I get the sum of each player's scores and display them side by side in one query?

Total Points Table

bob   mike
16     07

My (pseudo)-query is:

SELECT sum(points) as "Bob" WHERE name="bob",
       sum(points) as "Mike" WHERE name="mike"
  FROM score_table

Solution 1

You can pivot your data 'manually':

SELECT SUM(CASE WHEN name='bob' THEN points END) as bob,
       SUM(CASE WHEN name='mike' THEN points END) as mike
  FROM score_table

but this will not work if the list of your players is dynamic.

Solution 2

In pure sql:

SELECT
    sum( (name = 'bob') * points) as Bob,
    sum( (name = 'mike') * points) as Mike,
    -- etc
FROM score_table;

This neat solution works because of mysql's booleans evaluating as 1 for true and 0 for false, allowing you to multiply truth of a test with a numeric column. I've used it lots of times for "pivots" and I like the brevity.

Solution 3

Are the player names all known up front? If so, you can do:

SELECT SUM(CASE WHEN name = 'bob'  THEN points ELSE 0 END) AS bob,
       SUM(CASE WHEN name = 'mike' THEN points ELSE 0 END) AS mike,
       ... so on for each player ...
  FROM score_table

If you don't, you still might be able to use the same method, but you'd probably have to build the query dynamically. Basically, you'd SELECT DISTINCT name ..., then use that result set to build each of the CASE statements, then execute the result SQL.

Solution 4

This is called pivoting the table:

SELECT SUM(IF(name = "Bob", points, 0)) AS points_bob,
       SUM(IF(name = "Mike", points, 0)) AS points_mike
FROM score_table

Solution 5

SELECT sum(points), name
FROM `table`
GROUP BY name

Or for the pivot

SELECT sum(if(name = 'mike',points,0)),
       sum(if(name = 'bob',points,0))
FROM `table

View more solutions