6 minutes read

# The SQL COUNT() Function: A Detailed Guide

Updated on: April 22, 2024

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:

idnamepositionmanager_idhire_date
1Sara EvansData Analyst22019-11-16
2Anthony AdamsProduct ManagerNULL2021-07-09
3Brent FreemanMarketing CoordinatorNULL2019-12-15
4Karen TerrySoftware Engineer62021-08-21
5Kelly FrancisSales Associate32022-02-13
6Brandi HerreraSoftware Engineer22020-11-19

### 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.