How to Include Zero in a COUNT() Aggregate

You must encounter many occasions in your daily life where you must count on things, and counting things is rather simple, right? However, what would happen if you wanted to count something that’s not even there? That’s when the exciting part starts.

When you are using the COUNT() method in databases, there are situations in which you want to confirm that you count everything, even zero. In this tutorial, we will learn how to tackle this issue.

Understanding COUNT() Aggregate

Before getting into why to include zero, let’s first understand what the COUNT() function does. The COUNT() function in databases plays a very crucial role, it counts the rows that meet certain conditions in the table. For example, you can count all the rows that are present in a table, or you can also count the ones that have something specific in them.

COUNT(*) function counts all the rows present in a table, whereas COUNT(column_name) counts the number of non-null values present in a specific column.

Why Count Zero?

So, the first question in your mind could be ‘Why would anyone want to count zero?’ Now, let’s take a scenario where you’re having a birthday party, and you want to count how many pizzas each of your friends ordered. You want to count them, even if anyone does not order pizza. That’s where zero counting comes into the picture. Even if parts of it are empty, it helps to get the whole picture.

How to Count Zero?

Now you must be wondering, how to actually count zero in database. Well, there are multiple ways to do that:

Using LEFT JOIN
Using COALESCE

Now, let’s understand these one by one.

1. Using LEFT JOIN

In the LEFT JOIN, we merge the table from which we are counting with another table which contains all the possible value. After that, we just count. In this way, we can obtain a count that includes zero even in the case where the original table contains zero.

Example:

Imagine that you have two lists, in which one list contains all the names of your friends, and the other shows how many pizzas each friend has ordered. Using a LEFT JOIN is like placing up these lists side by side and matching the names. Even if someone’s name appears in the first list but not the second, they haven’t ordered any pizzas yet, they’ll still appear in the final count.

Below is the table named friends and pizzas_ordered:

Now let’s know which of your friends haven’t ordered a pizza yet, from the Friends and pizzas_ordered table:

SELECT 
    pizza_ordered.friend_id,
    COUNT(pizzas_ordered.pizzas_count) AS pizzas_ordered
FROM 
    friends
LEFT JOIN 
    pizzas_ordered 
ON friends.friend_id = pizzas_ordered.friend_id
GROUP BY 
    friends.friend_id, friends.name;Code language: SQL (Structured Query Language) (sql)

Friends With Their Pizzas 1 — Friends With Their Pizzas

Now we know that Amit hasn’t ordered any pizza yet as his name was not present at the second table and using LEFT JOIN we are able to figure out easily and count zero.

2. Using COALESCE

In this approach, we first create a mini-list which contains all possible values that we want to count. After that, we perform a COALESCE() operation on the original table, which refers to the mini list. This will ensure that the count includes zero even if there are no matching values in the original table. We have used it alongside the LEFT JOIN approach, let’s see it individually.

It is like saying “Hey, count how many pizzas each of my friends have ordered, and if they haven’t ordered any, just write zero” to the database.

Example:

Now, imagine a scenario where you have a record of messages sent by each user in a messaging system, and you have two tables: users and messages.

Now, you want to ensure that users who haven’t sent any messages are included in the result with a count of 0. Here’s how you can do this using COALESCE:

SELECT u.username,
       COALESCE(COUNT(m.message_id), 0) AS messages_sent
FROM users u
LEFT JOIN message m ON u.user_id = m.sender_id
GROUP BY u.user_id, u.username;
Code language: SQL (Structured Query Language) (sql)

In the above example, the COALESCE() function replaces NULL values (indicating users who haven’t sent any messages) with 0.

Conclusion

If you want to get a complete picture of your data, it’s crucial to include zero in your counts. I know it might seem a bit weird at first, but making this a habit will make sure that you never miss a thing. By using approaches like LEFT JOIN or COALESCE function, you can easily do that. So next time you are dealing with numbers in a database, make sure to count the zeros! We hope you enjoyed it.

Reference

https://stackoverflow.com/questions/74088255/how-to-include-zero-in-a-count-aggregate