How to sum based on field value in MySQL?

To sum based on field values, use aggregate function SUM() along with CASE statement. Let us first create a table −

mysql> create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Price int,
   isValidCustomer boolean,
   FinalPrice int
   );
Query OK, 0 rows affected (0.23 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(20,false,40);
Query OK, 1 row affected (0.09 sec)

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(45,true,10);
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(89,true,50);
Query OK, 1 row affected (0.09 sec)

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(200,false,100);
Query OK, 1 row affected (0.06 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+-------+-----------------+------------+
| Id | Price | isValidCustomer | FinalPrice |
+----+-------+-----------------+------------+
| 1  | 20    | 0               | 40         |
| 2  | 45    | 1               | 10         |
| 3  | 89    | 1               | 50         |
| 4  | 200   | 0               | 100        |
+----+-------+-----------------+------------+
4 rows in set (0.00 sec)

Following is the query to sum based on field value in MySQL. Here, FinalPrice would be added for FALSE (0), whereas PRICE would be added for TRUE(1) −

mysql> select sum(case when isValidCustomer=true then Price else FinalPrice end) as TotalPrice from DemoTable;

This will produce the following output −

+------------+
| TotalPrice |
+------------+
| 274        |
+------------+
1 row in set (0.00 sec)

Anvi Jain

Updated on 30-Jul-2019 22:30:26

• Related Questions & Answers
• SUM a column based on a condition in MySQL
• Aggregate based on array value to sum values in different MongoDB documents?
• Update a table based on StudentId value in MySQL?
• How to sum selected column values based on specific month records in MySQL?
• How to set default Field Value in MySQL?
• How to update field to add value to existing value in MySQL?
• How to derive value of a field from another field in MySQL?
• How to group objects based on a value in JavaScript?
• Reset MySQL field to default value?
• How to calculate time based on seconds in MySQL?
• How to swap a specific field value in MySQL?
• How can I count the documents in an array based on the value of a specific field?
• MySQL DATE_ADD() to increment a date based on the value in another column?
• Select data and set value to boolean based on timestamp column in MySQL
• MySQL query to fetch only a single field on the basis of boolean value in another field