MySQL count return 0 if no rows

MySQL count() function is used to returns the count of an expression. It allows us to count all rows or only some rows of the table that matches a specified condition. It is a type of aggregate function whose return type is BIGINT. This function returns 0 if it does not find any matching rows.

We can use the count function in three forms, which are explained below:

• Count (*)
• Count (expression)
• Count (distinct)

Let us discuss each in detail.

COUNT(*) Function: This function uses the SELECT statement to returns the count of rows in a result set. The result set contains all Non-Null, Null, and duplicates rows.

COUNT(expression) Function: This function returns the result set without containing Null rows as the result of an expression.

COUNT(distinct expression) Function: This function returns the count of distinct rows without containing NULL values as the result of the expression.

Syntax

The following are the syntax of the COUNT() function:

Parameter explanation

aggregate_expression: It specifies the column or expression whose NON-NULL values will be counted.

table_name: It specifies the tables from where you want to retrieve records. There must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.

MySQL count() function example

Consider a table named "employees" that contains the following data.

Let us understand how count() functions work in MySQL.

Example1

Execute the following query that uses the COUNT(expression) function to calculates the total number of employees name available in the table:

Output:

Example2

Execute the following statement that returns all rows from the employee table and WHERE clause specifies the rows whose value in the column emp_age is greater than 32:

Output:

Example3

This statement uses the COUNT(distinct expression) function that counts the Non-Null and distinct rows in the column emp_age:

Output:

MySQL Count() Function with GROUP BY Clause

We can also use the count() function with the GROUP BY clause that returns the count of the element in each group. For example, the following statement returns the number of employee in each city:

After the successful execution, we will get the result as below:

MySQL Count() Function with HAVING and ORDER BY Clause

Let us see another clause that uses ORDER BY and Having clause with the count() function. Execute the following statement that gives the employee name who has at least two age same and sorts them based on the count result:

Here’s the problem: you want to count something that doesn’t exist, and you want to show your result as zero. How do you do that in SQL?

Using the COUNT() aggregate function is a reasonable first step. It will count all the data it finds and return the number of occurrences. But what if there are no occurrences of certain data? It will not show up in the result. However, suppose you want to create a report that will also show data that has zero occurrences. How do you achieve that?

Does this problem seem too vague? I’ll show you a concrete example and a solution so you can see what I mean by including zero counts in SQL results. First, you should understand how aggregate functions work; if you’re not familiar with them, check out our Beginner’s Guide to SQL Aggregate Functions and Overview of SQL Aggregate Functions before continuing.

Example Tables

There are two tables I’ll use for my example: car_buyers and service_appointment.

The table car_buyers contains this data:

It’s a simple list of car buyers for a fictional car sale and repair shop.

The service_appointment table contains this data on various car service appointments:

What Do I Want?

Using these tables, I want to get a list of my car buyers along with the number of service appointments they had up until now. I also want that list to include those car buyers who haven’t had a service appointment yet, and I want to see a zero by their names. Those with zero appointments could be buyers that just bought a new car, so there hasn’t been enough time to need a service appointment.

If I do that, it means I’ve succeeded in including zero (0) in the COUNT() aggregate.

Not Much of a Solution

Intuitively, I might write this code in an attempt to solve the problem:

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb
JOIN service_appointment sa
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

What did I do here? I’ve included id,

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

Do you want to see what result this code returns? I’m sure you do. Take a look:

The result shows three buyers with a number of appointments. OK, that’s good. However, scroll up a little and you’ll see there are five records in the table car_buyers. I can conclude that the two buyers missing are those who have had zero service appointments up until now. However, I want my code to include them in this result table and I want to see explicitly that they’ve made zero appointments.

Here’s how a little tweaking of the above code can help.

The Real Solution - LEFT JOIN or RIGHT JOIN

Here’s the solution that will include zero counts in the result:

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

This is the same code as the previous one, but this time I’ve joined tables using the LEFT JOIN. Here’s what it returns:

That’s a nice surprise! There are all five car buyers, and the table also shows that Susan Santana and Dolores Raich haven’t had any car service appointments.

How does this work? It’s all about the JOIN type. Using the suitable JOIN is crucial when you want to include zeros in the COUNT() aggregate.

If you know how the LEFT JOIN works, it’s easy for you to understand why this code returns the result with zeros.

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

If you now use the aggregate function COUNT(), like in the code above, it will not count the NULL values and the result will be zero. So, it’s also important that you know how COUNT() works in various circumstances.

You can get the same result using the

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

Everything else can stay the same. If you’re not familiar with using

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

Now You Know How to Get Zeros in Your SQL Result

You see that the main point here is not so much using the aggregate function COUNT(). The game changer is the kind of

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM car_buyers cb 
LEFT JOIN service_appointment sa 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

SELECT	cb.id,
		cb.first_name,
		cb.last_name,
		COUNT(sa.id) AS no_of_appointments
FROM  service_appointment sa
RIGHT JOIN car_buyers cb 
ON cb.id = sa.car_buyer_id
GROUP BY cb.id, cb.first_name, cb.last_name;

You can learn all about JOINs and their differences in our SQL JOINs course, which is a part of the SQL from A to Z track. This will not only allow you to show zero counts, but also help you to become a master at joining tables in SQL.

How to display zero as count if there is no record in database?

Does count in SQL count 0?

How to show months if it has no record and force it to zero if NULL on MySQL?

Does count in MySQL count NULL values?