MySQL COUNT IF

Summary: in this tutorial, you will learn how to use the MySQL COUNT IF to count values in a set based on a condition.

Introduction to MySQL COUNT IF function

The IF function evaluates an expression and returns a value depending on whether the result of the expression is true or false.

Here’s the syntax of the IF function:

IF(expression, value_if_true, value_if_false)Code language: SQL (Structured Query Language) (sql)

In this syntax, the IF function returns value_if_true if the expression is true or value_if_false if the expression is false.

The COUNT() function is an aggregate function that returns the number of non-null values in a set. Because the COUNT() function accepts an expression, you can use the IF to form an expression like this:

COUNT(IF(condition,1, NULL));Code language: SQL (Structured Query Language) (sql)

In this syntax, the COUNT() will return the number of values that make the condition true. Note that you can use another value other than the number 1.

MySQL COUNT IF example

First, create a new table called orders with four columns order_id, customer_name, order_date, and order_status:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_date DATE,
    order_status VARCHAR(20)
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the orders table with different order statuses:

INSERT INTO orders (customer_name, order_date, order_status)
VALUES
    ('Alice', '2023-01-15', 'Shipped'),
    ('Bob', '2023-02-20', 'Pending'),
    ('Charlie', '2023-03-10', 'Shipped'),
    ('David', '2023-04-05', 'Delivered'),
    ('Eve', '2023-05-12', 'Shipped');Code language: SQL (Structured Query Language) (sql)

Third, query data from the orders table:

SELECT * FROM orders;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+---------------+------------+--------------+
| order_id | customer_name | order_date | order_status |
+----------+---------------+------------+--------------+
|        1 | Alice         | 2023-01-15 | Shipped      |
|        2 | Bob           | 2023-02-20 | Pending      |
|        3 | Charlie       | 2023-03-10 | Shipped      |
|        4 | David         | 2023-04-05 | Delivered    |
|        5 | Eve           | 2023-05-12 | Shipped      |
+----------+---------------+------------+--------------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

Finally, count the orders by status using the COUNT IF to count the number of shipped orders:

SELECT 
  COUNT(
    IF(order_status = 'Shipped', 1, NULL)
  ) AS shipped_orders_count 
FROM 
  orders;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------+
| shipped_orders_count |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

There are three shipped orders.

In this example:

• The IF(order_status = 'Shipped', 1, NULL) returns 1 if the order_status is shipped or NULL otherwise.
• The COUNT will count only 1 and ignore the NULL value. Therefore, it returns the number of shipped orders.

Similarly, you can use the COUNT IF to counter the number of orders by status:

SELECT 
  COUNT( IF(order_status = 'Shipped', 1, NULL)) AS shipped_orders_count, 
  COUNT( IF(order_status = 'Pending', 1, NULL)) AS pending_orders_count, 
  COUNT( IF(order_status = 'Delivered', 1, NULL)) AS delivered_orders_count 
FROM 
  orders;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------+----------------------+------------------------+
| shipped_orders_count | pending_orders_count | delivered_orders_count |
+----------------------+----------------------+------------------------+
|                    3 |                    1 |                      1 |
+----------------------+----------------------+------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use MySQL COUNT IF to count a number of values based on a specific condition.