Select multiple sums with MySQL query and display

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

you can use pivot function also for the same thing .. even by performance vise it is better option to use pivot for pivoting... (i am talking about oracle database)..

you can use following query for this as well.. -- (if you have only these two column in you table then it will be good to see output else for other additional column you will get null values)

select * from  game_scores
pivot (sum(points) for name in ('BOB' BOB, 'mike' MIKE));

in this query you will get data very fast and you have to add or remove player name only one place

:)

if you have more then these two column in your table then you can use following query

 WITH pivot_data AS (
            SELECT points,name 
  FROM   game_scores
  )
  SELECT *
  FROM   pivot_data
pivot (sum(points) for name in ('BOB' BOB, 'mike' MIKE));

Answer 5

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.

Answer 6

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