Perform conditional summation using the SUM and IF functions.
MySQL SUM IF
Summary: in this tutorial, you will learn about the MySQL SUM IF function to perform conditional summation.
Introduction to MySQL SUM IF functions
The SUM() function is an aggregate function that returns the total of values in a column of a table.:
SELECT SUM(column_name)
FROM table_name;Code language: SQL (Structured Query Language) (sql)The IF function is a flow control function that returns a value if a condition is true and another value if the expression is false:
IF(condition, value_if_true, value_if_false)Code language: SQL (Structured Query Language) (sql)When you combine the SUM function with the IF function, you can perform conditional summation, making it a powerful tool for data analysis.
Here’s the basic syntax of the SUM IF:
SELECT SUM(IF(condition, value_to_sum, 0))
FROM table_name;Code language: SQL (Structured Query Language) (sql)In this syntax:
condition: The condition that you want to apply.value_to_sum: The value that you want to sum if the condition is true.0: The value to sum if the condition is false. You can change it to any default value you want.
MySQL SUM IF example
Let’s take an example of using the SUM IF.
First, create a new table named sales with the following structure:
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
sale_date DATE,
amount DECIMAL(10, 2)
);Code language: SQL (Structured Query Language) (sql)The sales table has four columns:
idfor a unique identifier.product_nameto store the product name.sale_dateto store the date of the sale.amountto store the sale amount.
Second, insert some rows into the sales table:
-- Insert data for OctoberINSERT INTO sales (product_name, sale_date, amount)
VALUES
('Phone', '2023-10-01', 200.00),
('Tablet', '2023-10-05', 150.00),
('Phone', '2023-10-10', 220.00),
('Tablet', '2023-10-15', 180.00),
('Phone', '2023-10-20', 250.00),
('Tablet', '2023-10-25', 190.00),
('Phone', '2023-11-02', 210.00),
('Tablet', '2023-11-07', 160.00),
('Phone', '2023-11-12', 230.00),
('Tablet', '2023-11-18', 170.00),
('Phone', '2023-11-23', 240.00),
('Tablet', '2023-11-28', 200.00),
('Phone', '2023-12-03', 190.00),
('Tablet', '2023-12-08', 140.00),
('Phone', '2023-12-13', 210.00),
('Tablet', '2023-12-19', 160.00),
('Phone', '2023-12-24', 230.00),
('Tablet', '2023-12-29', 180.00);Code language: SQL (Structured Query Language) (sql)Third, calculate the total sales amount for “Phone” in October 2023
SELECT
SUM(
IF(
product_name = 'Phone'
AND MONTH(sale_date) = 10
AND YEAR(sale_date) = 2023,
amount,
0
)
) AS total_sales
FROM
sales;Code language: SQL (Structured Query Language) (sql)Output:
+-------------+
| total_sales |
+-------------+
| 670.00 |
+-------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Here’s how the query works.
First, the SUM function calculates the sum of values.
Second, the IF function checks three conditions:
product_name = 'Phone': This ensures only rows withPhoneare considered.MONTH(sale_date) = 10: It checks if the sale date is in October (10).YEAR(sale_date) = 2023: It checks if the year is2023.
The query returns the total sales amount for Phone in October 2023.
Summary
- Use the MySQL
SUMIFto perform a conditional summation.
