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

To really make this pivot dynamic, I would do a straight group by and do the pivot outside of mysql. Programming a dynamic pivot isn't so easy in mysql. — Jacob Eggers, Jun 21 '11 at 00:10

manji · Accepted Answer · 2011-06-21T00:08:02.613

29

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.

edited Jun 21 '11 at 00:08

answered Jun 20 '11 at 23:57

manji

47,442
5
96
103

If you group by name, he gets a diagonal matrix. – AndreKR Jun 20 '11 at 23:58
This was very helpful, @manji. I had a similar problem, and I tried using something like: "IF(name='bob',SUM(points),0) as bob" and it didn't work since the SUM() combines the rows to a single one and the IF() is done afterwards, or so it seems. – nomæd Mar 19 '14 at 14:45

Bohemian · Answer 2 · 2011-06-21T23:05:21.533

8

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.

edited Jun 21 '11 at 23:05

answered Jun 20 '11 at 23:57

Bohemian

412,405
93
575
722

score 5 · Answer 3 · answered Jun 20 '11 at 23:58

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.

score 3 · Answer 4 · answered Jun 20 '11 at 23:56

3

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

answered Jun 20 '11 at 23:56

AndreKR

32,613
18
106
168

I don't vote for non-ANSI syntax, but you're otherwise correct – OMG Ponies Jun 21 '11 at 00:03
He stated he's using MySQL and I actually do like the function-like syntax as it is shorter. – AndreKR Jun 21 '11 at 00:08

Jacob Eggers · Answer 5 · 2011-06-20T23:58:19.033

1

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

edited Jun 20 '11 at 23:58

answered Jun 20 '11 at 23:52

Jacob Eggers

9,062
2
25
43

2

he wants a PIVOT so the names become columns, not a group by – Tahbaza Jun 20 '11 at 23:54

pratik garg · Answer 6 · 2011-06-21T13:41:24.943

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));

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

6 Answers6

Linked