SQL COUNT WHERE

When working in SQL, you are bound to come across instances where you need to count the number of rows in a given table. For example, if you are dealing with records without a numbered ID, you need to use other methods in order to determine the total number of rows.

Luckily, we have access to the SQL count() function which plays a crucial role in helping us to determine the number of rows in a table or the result set of a given query.

In this guide, we will dive into the various usages and examples on how to work with the count() function to count the number of rows in a given table or result set.

Requirements:

Before we dive into the queries and examples, it is good to ensure that we have the requirements. For this post, we are going to use the following:

a. MySQL database version 8.0
b. MySQL Sakila sample database

You can reference the documentation or check our tutorial on how to install and setup the Sakila sample database on your server.

NOTE: You can also use any dataset that you wish. The methods that are discussed in this post will apply to all.

SQL Count Function

As we mentioned, we use the count() function to determine the number of rows in a table or result set of a query. We can express the function syntax as follows:

COUNT(expression)

Here, the expressions refers to an optional argument that specifies what to count. We can provide the following arguments:

a. * – It tells the function to count all the rows in the table or result set.
b. column_name – This counts the number of non-null values in the specified column.
c. DISTINCT column_name – This counts the number of distinct, non-null values in the specified column.

Example 1: Count All Rows

The most simplistic usage of the count() function is to count all the rows in a given table.

Take the following query for example that demonstrates how to use this function to determine the number of rows in the “film” table from the Sakila sample database:

SELECT COUNT(*) AS total_films
FROM film;

This should count and return the total number of records in the specified table. An example output is as follows:

total_films|
-----------+
1000|

Example 2: Conditional Count (WHERE)

The function also allows us to specify a given Boolean condition. This returns the total number of rows that match that specific condition.

Take the “film” table from the Sakila database for example. Suppose we wish to determine the number of films whose rating is equal to “R”.

We can run a query as follows:

SELECT COUNT(*) AS total_rated_r_films
FROM film
WHERE rating = 'R';

In this case, we add a conditional using the WHERE clause. This should filter for only the matching records and pass the result set to the count() function.

An example output is as follows:

total_rated_r_films|
-------------------+
195|

Conclusion

In this tutorial, we learned how to work with the count() function in SQL in conjunction with the WHERE clause for conditional filtering.