Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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
MySQL query to sum 3 different values in a column displaying total of each value in result set?
For this, you can use a CASE statement. Let us first create a table −
mysql> create table DemoTable
(
ProductName varchar(100),
ProductRating ENUM('1','2','3')
);
Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −
mysql> insert into DemoTable values('Product-1',3);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Product-2',1);
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('Product-3',2);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Product-1',2);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Product-3',3);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Product-2',2);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Product-3',3);
Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+-------------+---------------+ | ProductName | ProductRating | +-------------+---------------+ | Product-1 | 3 | | Product-2 | 1 | | Product-3 | 2 | | Product-1 | 2 | | Product-3 | 3 | | Product-2 | 2 | | Product-3 | 3 | +-------------+---------------+ 7 rows in set (0.00 sec)
Here is the query to sum 3 different values in a column displaying a total of each value in the result set. We are adding on the basis of Product Rating −
mysql> select ProductName, sum( case when ProductRating=3 then 1 else 0 end ) as Product_3_Rating, sum( case when ProductRating=2 then 1 else 0 end ) as Product_2_Rating, sum( case when ProductRating=1 then 1 else 0 end ) as Product_1_Rating from DemoTable group by ProductName;
This will produce the following output −
+-------------+------------------+------------------+------------------+ | ProductName | Product_3_Rating | Product_2_Rating | Product_1_Rating | +-------------+------------------+------------------+------------------+ | Product-1 | 1 | 1 | 0 | | Product-2 | 0 | 1 | 1 | | Product-3 | 2 | 1 | 0 | +-------------+------------------+------------------+------------------+ 3 rows in set (0.00 sec)
Updated on: 01-Oct-2019
127 Views
Advertisements