SQL Distinct

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

MySQL database version 8.0
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)

The expression refers to an optional argument that specifies what to count. We can provide the following arguments:

* – This tells the function to count all the rows in the table or result set.
column_name – This counts the number of non-null values in the specified column.
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 for example the following query 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

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

Take for example the “film” table from the Sakila database. 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|

Example 3: Count the Distinct Values

Let us say you want to count the number of unique values in a given column without doing unnecessary joins and filtering. You can do this using the count() function and passing the DISTINCT clause as the argument.

An example usage is as follows:

SELECT COUNT(DISTINCT rating) AS unique_ratings

FROM film;

This should return the number of unique film ratings.

Output:

unique_ratings|
--------------+
5|

Conclusion

This tutorial explored the count() function to count the number of rows in a given table or result set. We also focused on the count which is distinct to count the unique number of rows in the given table or result set.