
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- How to sum selected column values based on specific month records in MySQL?
- 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 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 swap a specific field value in MySQL?
- How to calculate time based on seconds 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
- How to group objects based on a value in JavaScript?
- How to Auto-Strikethrough Based on Cell Value in Excel?
- How to change value based on cell color in Excel?

Advertisements