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.

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.

Let’s take a table of 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 whose 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:

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:

+---------------------+
| shipped_items_count |
+---------------------+
|                   2 |
+---------------------+

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 the 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 the 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:

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:

+-------------------+
| high_value_orders |
+-------------------+
|                 3 |
+-------------------+

Explanation: As we can see from the table there are 3 rows with an amount of more than 350. There are 3 rows with amounts 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 for Customers with columns customer_id, first_name, last_name, age, and country. In this example, we will see how to work with the 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:

1. Counting Rows Based on a Single Condition

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

Output:

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

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

2. Counting Rows Based on Multiple Conditions

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

Output:

+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

Explanation: As we can see there is one value where the 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:

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

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:

+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

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

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.

What is the purpose of the COUNT() function in MySQL?

The COUNT() function in MySQL is used to count the number of rows that match a specified condition or simply count all rows in a table. It is an aggregate function that helps in determining the size of a result set.

How can I count rows in a table based on a specific condition using COUNT()?

You can count rows based on a specific condition using the COUNT() function along with the WHERE clause. For example:

SELECT COUNT(*) FROM table_name WHERE condition;

This query counts only the rows that meet the specified condition.

Can COUNT() be used to count non-null values in a specific column?

Yes, COUNT() can count non-null values in a specific column. You can specify the column name as an argument to the COUNT() function:

SELECT COUNT(column_name) FROM table_name WHERE condition;

This counts only the non-null values in column_name that meet the condition.

What happens if there are no rows that match the condition in the COUNT() function?

If no rows match the condition specified in the COUNT() function, it returns 0. The function does not return an error; instead, it simply indicates that no rows meet the criteria.