Count the number of values that meet a specified condition.
MySQL Function: COUNT(IF)
Counts values meeting specific conditions
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)
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 theorder_statusisshippedorNULLotherwise. - The
COUNTwill count only 1 and ignore theNULLvalue. 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
COUNTIFto count a number of values based on a specific condition.
