Count Rows in SQL

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)

Here, the expressions 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|

Example 4: Count the Rows in Joined Table (Advanced)

In other instances, you might need to count the number of rows in a table join. Although it may seem complex at first, with a few query formatting, you can use the count function to accomplish this in one statement.

For example, suppose we wish to count the number of films that are rented by each customer. We can run a query as follows:

SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS total_rentals

FROM customer AS c

LEFT JOIN rental AS r ON c.customer_id = r.customer_id

GROUP BY c.customer_id, c.first_name, c.last_name

ORDER BY total_rentals DESC;

In the given example, we use a LEFT JOIN on the customer and the rental table. This links the customers with their film rentals.

We then use the count() function to determine the number of rentals for each customer.

We also use the GROUP BY clause to group the resulting set by the customer and order the total rentals from the highest to lowest (descending order).

The resulting output is as follows:

customer_id|first_name |last_name |total_rentals|
-----------+-----------+------------+-------------+
148|ELEANOR |HUNT | 46|
526|KARL |SEAL | 45|
144|CLARA |SHAW | 42|
236|MARCIA |DEAN | 42|
75|TAMMY |SANDERS | 41|
469|WESLEY |BULL | 40|
197|SUE |PETERS | 40|
137|RHONDA |KENNEDY | 39|
178|MARION |SNYDER | 39|
468|TIM |CARY | 39|
5|ELIZABETH |BROWN | 38|
459|TOMMY |COLLAZO | 38|
295|DAISY |BATES | 38|
410|CURTIS |IRBY | 38|
176|JUNE |CARROLL | 37|
198|ELSIE |KELLEY | 37|
257|MARSHA |DOUGLAS | 37|
366|BRANDON |HUEY | 37|

Conclusion

In this tutorial, we walked you through the usage of the count() function in SQL to determine the number of rows in a given table.