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:

• id for a unique identifier.
• product_name to store the product name.
• sale_date to store the date of the sale.
• amount to store the sale amount.

Second, insert some rows into the sales table:

-- Insert data for October
INSERT 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 with Phone are considered.
• MONTH(sale_date) = 10: It checks if the sale date is in October (10).
• YEAR(sale_date) = 2023: It checks if the year is 2023.

The query returns the total sales amount for Phone in October 2023.

Summary

• Use the MySQL SUM IF to perform a conditional summation.