The SQL COUNT() Function: A Detailed Guide

This guide will give you a detailed explanation (with examples) of all the typical uses of the COUNT() function. Exercises included!

The COUNT() function in SQL is one of the most commonly used aggregate functions. Aggregate functions in SQL are used to calculate statistics for a group of rows: counting the number of rows in each group, computing the sum of values in a group, finding the minimum or maximum value in a group, and so on.

The SQL COUNT() function returns the number of rows returned by a query. In practice, the COUNT() function can help you calculate the number of films in a database, the number of films in a specific genre, the number of films per director, etc.

If you're new to aggregate functions, I recommend our SQL Basics course. It will teach you the basics of the COUNT() function and other aggregate functions. If you're a seasoned SQL user, check out our SQL Practice track with interactive SQL practice exercises on various topics, including COUNT().

Using COUNT(*) – Counting Rows in the Result

The most common way to use COUNT() is to use it with the asterisk (*) as the argument. The asterisk tells the COUNT() function to return the number of rows in the result set.

Example 1: Using COUNT(*) to Count All Rows in a Table

The following SQL statement uses COUNT(*) to count the number of rows in the table films:

SELECT COUNT(*)
FROM films;

Example 2: Using COUNT(*) to Count Rows in a Result Set

It’s important to emphasize that the COUNT(*) function returns the number of rows in the result set of the query. The following SQL statement returns the number of films that have Woody Allen as their director:

SELECT COUNT(*)
FROM films
WHERE director = 'Woody Allen';

Using COUNT(expression)

Another common way to use the SQL COUNT() function is to use it with an expression. With this syntax, the function returns the number of non-NULL values in a given column.

Example 3: Counting Non-NULL Values in a Column

Look at this example:

SELECT COUNT(release_date)
FROM films;

Here, the function returns the number of non-NULL release dates for films. If the release date for a certain film is NULL, the film will not be counted. If the release date is not NULL, it will be counted.

Example 4: Counting Distinct Values with SQL COUNT(DISTINCT)

You can use the SQL COUNT(DISTINCT column_name) to count the number of distinct values in a column. The following SQL statement will count the number of distinct directors in the films table:

SELECT COUNT(DISTINCT director)
FROM films;

To count unique (non-duplicate) values in a certain column, you put the DISTINCT keyword inside COUNT(), followed by the name of the column. With this syntax, the function returns the number of different non-NULL values in a dataset. Any duplicate values will not be included in the count.

Summary: SQL COUNT() Function Syntax

The most common way to use COUNT() function is with COUNT(*):

SELECT COUNT(*)
FROM table
WHERE ...

An alternative way is to use the COUNT() function with an expression.

SELECT COUNT([ALL | DISTINCT] expression)
FROM table
WHERE ...

The ALL option is the default, it instructs the COUNT() function to count non-NULL values in an expression. The DISTINCT option is non-default and it instructs the COUNT() function to only count distinct (unique) values of an expression.

To explore the differences between variants of the COUNT() syntax, check out our article What is the Difference Between COUNT(*), COUNT(1), COUNT(column name), and COUNT(DISTINCT column name)?.

Using SQL COUNT() and GROUP BY

Typically, the COUNT() function is used with the GROUP BY clause. GROUP BY is a clause that groups all the rows with the same value. Usually, groups are specified columns of the dataset.

Here’s an example of GROUP BY’s usage with COUNT(*):

SELECT 
  director,
 COUNT(*) AS film_count
FROM films
GROUP BY director
ORDER BY director;

We want to show all the directors and the number of films directed by them. We select the director name and use COUNT(*) to show the number of films. Then we specify the language column in GROUP BY.

If you need some more examples, check out how to use GROUP BY with SQL aggregate functions.

COUNT() Function Exercises

Here are some exercises for you to practice SQL COUNT() function. We’ll work with the employees table:

Exercise 1: How many different positions are there in the employees table?

Solution:

SELECT COUNT(DISTINCT position)
FROM employees;

Solution explanation:

To count different positions in the employees table, we use the SQL COUNT() function with the DISTINCT keyword. Since we want to count the number of different positions, we give the column position as an argument to the COUNT() function.

Exercise 2: How many employees work in each position?

Solution:

SELECT 
  position,
  COUNT(*)
FROM employees
GROUP BY position;

Solution explanation:

To count the number of employees in each position, we use GROUP BY position to group the employees with the same position into one group. We select the position name and use COUNT(*) to count the number of rows in each group. This way the COUNT() function returns the number of employees in each position.

Exercise 3: How many employees do not have a supervisor?

Solution:

SELECT COUNT(*) - COUNT(manager_id)
FROM employees;

Solution explanation:

This exercise requires some thinking. We can use COUNT(*) to count all employees in the table. We can also use COUNT(manager_id) to count the number of employees with a supervisor: COUNT(manager_id) will count all employees with a non-empty manager_id value, finding employees with a manager. We then subtract one value from another to find the number of employees without a supervisor.

You can find even more COUNT() and GROUP BY exercises in our article: 10 GROUP BY Practice Exercises with Detailed Solutions.

COUNT() Is as Easy as One, Two, Three!

SQL COUNT() is not difficult to understand. There are several ways of using it, and each might return a different result.

Choosing how to use COUNT() becomes easier with practice. Our SQL Practice track is designed for exactly that purpose. You could also try these seven examples of the SQL COUNT() function.

Aside from practice, it’s important that you know your data and what you want to achieve with it. When all this is clear to you, COUNT() really becomes an easy function. You can count on that! Pun intended.