- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- Select multiple columns and display in a single column in MySQL?
- MySQL query to display ranks of multiple columns?
- MySQL query to separate and select string values (with hyphen) from one column to different columns
- MySQL select query with multiple WHERE?
- How to select different values from same column and display them in different columns with MySQL?
- MySQL query to select the values having multiple occurrence and display their count
- MySQL Select Statement DISTINCT for Multiple Columns?
- Select distinct values from three columns and display in a single column with MySQL
- Display the sum of positive and negative values from a column in separate columns with MySQL
- Change multiple columns in a single MySQL query?
- MySQL query to GROUP BY multiple columns
- MySQL query to select multiple rows effectively?
- MySQL query to select all the records only from a specific column of a table with multiple columns
- MySQL multiple COUNT with multiple columns?
- MySQL query to sort multiple columns together in a single query

Advertisements