In this lesson, you will learn how to write a SQL COUNT statement.

What is a SQL COUNT Statement?

A SQL COUNT statement returns the number of input rows that match a specific condition of a query.

The COUNT statement can be applied to a single column to measure how many rows it contains, like this:

SELECT COUNT(column_name) FROM table_name;

The COUNT statement can also be used to count the number of rows in the entire table, like this:

SELECT COUNT(*) FROM table_name;

Note that the two code cells presented above will return the same value in the vast majority of cases.

Unlike the other statements that we have seen so far in this course, the COUNT statement does not return back a column from a database. Instead, it returns back a single number.

To see an example of this, the following screenshot shows the COUNT statement applied to the payment table from our DVD Rental database:

The SQL COUNT statement becomes significantly more useful when combined with other SQL queries. We explore this in the next section.

Combining SQL COUNT and SQL DISTINCT

You can combine the SQL COUNT statement with the DISTINCT keyword from the last lesson to count the number of distinct entries in a column.

As an example, you could count the number of distinct customers who have processed payments to our DVD store with the following query:

SELECT COUNT(DISTINCT customer_id) FROM payment;

Alternatively, you could count the number of distinct DVDs that have been paid for using the following query:

SELECT COUNT(DISTINCT rental_id) FROM payment;

Note that for readability's sake, you could also wrap rental_id in brackets like this:

SELECT COUNT(DISTINCT(rental_id)) FROM payment;

This is not necessary and will generate the same output, but some SQL programmers find this to be more readable and it is merely a matter of taste.

You might also be wondering why the COUNT keyword needs brackets after it. The reason for this is that without the brackets, you will have the following code:

SELECT COUNT DISTINCT customer_id FROM payment;

In this case, the COUNT statement will try to count the number of entries in a column called DISTINCT, which does not exist. This is why the COUNT statement requires parentheses when combined with the DISTINCT keyword.

Final Thoughts

In this lesson, you learned how to count rows and distinct values in SQL using the COUNT statement. In the next lesson, we will learn how to select subsets of a database table using the SELECT WHERE statement.