How to Count Based on Condition in MySQL?

The Count() function in MYSQL is an inbuilt function that helps count the number of rows or values in a table. It can count based on the specific conditions given by the user which can help in targetted operations. There are certain times when we just need the values that satisfy a specific condition and not all the values of the column. This function is available in the 4.0 and above versions of MySQL.

In this article, This article delves into the concept of conditional counting in MySQL and demonstrates its practical usage. we will see the concept of the count() function with specific conditions and see the syntax with the output.

COUNT() with Condition in MySQL

The count() functions without any condition will just count all the rows in a specified table. But since we need to see the syntax when we put some conditions in the function. The general syntax looks like this:

Syntax:

SELECT COUNT(expression)

FROM table

WHERE condition;

• COUNT(): COUNT() is an aggregate function used to count the number of rows in a result set.
• expression: This parameter specifies the column or expression whose non-null values will be counted. It can be an asterisk (*) to count all rows or a specific column.

Another way to show the COUNT() Function:

Syntax:

SELECT COUNT(CASE WHEN condition THEN 1 ELSE NULL END) AS count_alias
FROM your_table;

Here, if the case statement is true then the count is incremented or else it is not incremented.

Examples of Count() in MySQL

Let’s take a table Shippings with column names as shipping_id, status and customer. We will add some values to all the columns and use the count() function to count the number of customers whose status is ‘Pending‘ and customer is greater than 3.

CREATE TABLE Shippings (
    shipping_id INT PRIMARY KEY,
    status VARCHAR(50),
    customer INT,
);

There are some values inserted in the table and it looks something like this.

INSERT INTO Shippings(shipping_id, status,customer)
VALUES 
    (1,'Pending',2),
    (2,'Pending',4),
    (3,'Delivered',3),
    (4,'Pending',5),
    (5,'Delivered',1);

Output:

Shippings table

Example 1: Count of Shipped Items with ‘Pending’ Status for Customers > 3

Now we apply the count() function with the condition. We will be using the first method here.

SELECT COUNT(*) AS shipped_items_count
FROM Shippings
WHERE status = 'Pending' AND customer >3;

Output:

output

Explanation: As we can see there are two rows with status as pending and customer value more than 3. One with the shipping_id as 2 and the other one with 4. The output comes as 2 as shown above which is the count.

Example 2: Count of High-Value Orders in the “Orders” Table

Let’s take a table with name Orders and columns as order_id, item, amount and customer_id. We will insert some values into the table and count the values when amount is greater than 350.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    item VARCHAR(50),
    amount INT,
    customer_id INT
);

There are some values inserted in the table and it looks something like this.

INSERT INTO Orders(order_id,item,amount,customer_id)
VALUES 
    (1,'Keyboard',400,4),
    (2,'Mouse',300,4),
    (3,'Monitor',12000,3),
    (4,'Keyboard',400,1),
    (5,'Mousepad',250,2);

Output:

orders table

We will be using the second method here for counting the values.

SELECT COUNT(CASE WHEN amount > 350 THEN 1 ELSE NULL END) AS high_value_orders
FROM Orders;

Output:

output

Explanation: As we can see from the table there are 3 rows with amount more than 350. There are 3 rows with amount more than 350 with order_id 1.3 and 4. Hence the output comes as 3.

Example 3: Customer Counts Based on Different Conditions

Let’s take a table Customers with columns customer_id, first_name, last_name, age and country. In this example we will see how to work with count() function based on certain conditions.

CREATE TABLE Customers(
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    country VARCHAR(50)
);

We will insert some values into the table

INSERT INTO Customers(customer_id,first_name,last_name,age,country)
VALUES 
    (1,'John','Doe',31,'USA'),
    (2,'Robert','Luna',22,'USA')),
    (3,'David','Robinson',22,'UK')),
    (4,'John','Reinhardt',25,'Uk')),
    (5,'Betty','Doe',28,'Uae'));

Output:

Customers table

1. Counting Rows Based on a Single Condition

SELECT COUNT(*) FROM Customers WHERE country = 'USA';

Output:

output

Explanation: As we can see that there are 2 values where country is USA. Hence the output is 2.

2. Counting Rows Based on Multiple Conditions

SELECT COUNT(*) FROM Customers WHERE country = 'USA' AND age > 30;

Output:

output

Explanation: As we can see there is one value where country is USA and age is greater than 30. Hence the output comes as 1.

3. Counting Rows with NULL Values

SELECT COUNT(*) FROM Customers WHERE last_name IS NULL;

Output:

output

Explanation: As we can see there are 0 values where last name is null. Hence the output is 0.

4. Counting Rows Excluding NULL Values

SELECT COUNT(*) FROM Customers WHERE last_name IS NOT NULL;

Output:

output

Explanation: There are 5 values where last_name is not NULL. Hence, the output is 5.

Conclusion

In conclusion, MySQL allows you to specify conditions within the COUNT() function, offering a flexible approach to counting records based on specific criteria. This feature enhances the functionality of COUNT(), providing a powerful tool for tailored data analysis in MySQL queries.The COUNT() function with conditions in MYSQL makes it easy for users to work with the database and enhances its user-friendliness.