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

To select multiple sum columns with MySQL query and display them in separate columns, you need to use CASE statement. The syntax is as follows:

SELECT
SUM( CASE WHEN yourColumnName1=’yourValue1’ THEN yourColumnName2 END ) AS yourSeparateColumnName1,
SUM( CASE WHEN yourColumnName1=’yourValue2’ THEN yourColumnName2 END ) AS yourSeparateColumnName2,
SUM( CASE WHEN yourColumnName1=’yourValue3’ THEN yourColumnName2 END ) AS yourSeparateColumnName3,
.
.
.
N
FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table selectMultipleSumDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> PlayerName varchar(20),
   -> PlayerScore int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.58 sec)

Now you can insert some records in the table using insert command. The query is as follows:

mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Maxwell',89);
Query OK, 1 row affected (0.23 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Ricky',98);
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Maxwell',96);
Query OK, 1 row affected (0.18 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Ricky',78);
Query OK, 1 row affected (0.16 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Maxwell',51);
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Ricky',89);
Query OK, 1 row affected (0.21 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('David',56);
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('David',65);
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from selectMultipleSumDemo;

The following is the output:

+----+------------+-------------+
| Id | PlayerName | PlayerScore |
+----+------------+-------------+
|  1 | Maxwell    |          89 |
|  2 | Ricky      |          98 |
|  3 | Maxwell    |          96 |
|  4 | Ricky      |          78 |
|  5 | Maxwell    |          51 |
|  6 | Ricky      |          89 |
|  7 | David      |          56 |
|  8 | David      |          65 |
+----+------------+-------------+
8 rows in set (0.00 sec)

The query to get a separate column with multiple sum:

mysql> select
   -> SUM(CASE WHEN PlayerName='Maxwell' THEN PlayerScore END) AS 'MAXWELL TOTAL SCORE',
   -> SUM(CASE WHEN PlayerName='Ricky' THEN PlayerScore END) AS 'RICKY TOTAL SCORE',
   -> SUM(CASE WHEN PlayerName='David' THEN PlayerScore END) AS 'DAVID TOTAL SCORE'
   -> from selectMultipleSumDemo;

The following is the output:

+---------------------+-------------------+-------------------+
| MAXWELL TOTAL SCORE | RICKY TOTAL SCORE | DAVID TOTAL SCORE |
+---------------------+-------------------+-------------------+
|                 236 |               265 |               121 |
+---------------------+-------------------+-------------------+
1 row in set (0.00 sec)

Ankith Reddy

Published on 27-Feb-2019 05:49:33

Previous Page Print Page