SQL: COUNT Function

This SQL tutorial explains how to use the SQL COUNT function with syntax, examples, and practice exercises.

Description

The SQL COUNT function is used to count the number of rows returned in a SELECT statement.

Syntax

The syntax for the COUNT function in SQL is:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

OR the syntax for the COUNT function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within the COUNT function and must be included in the GROUP BY clause at the end of the SQL statement.

aggregate_expression

This is the column or expression whose non-null values will be counted.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

ORDER BY expression

Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - COUNT Function only includes NOT NULL Values

Not everyone realizes this, but the COUNT function will only count the records where the expression is NOT NULL in COUNT(expression). When the expression is a NULL value, it is not included in the COUNT calculations. Let's explore this further.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following SELECT statement that uses the COUNT function:

SELECT COUNT(customer_id)
FROM customers;

There will be 1 record selected. These are the results that you should see:

COUNT(customer_id)
6

In this example, the query will return 6 since there are 6 records in the customers table and all customer_id values are NOT NULL (ie: customer_id is the primary key for the table).

But what happens when we encounter a NULL value with the COUNT function? Let's enter this next SELECT statement that counts the favorite_website column which can contain NULL values:

SELECT COUNT(favorite_website)
FROM customers;

There will be 1 record selected. These are the results that you should see:

COUNT(favorite_website)
5

This second example will return 5. Because one of the favorite_website values is NULL, it would be excluded from the COUNT function calculation. As a result, the query will return 5 instead of 6.

TIP: Use the primary key in the COUNT function or COUNT(*) if you want to be certain that records aren't excluded in the calculations.

Example - Using a Single Expression in the COUNT Function

Let's look at an example that shows how to use the COUNT function with a single expression in a query.

In this example, we have a table called employees with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

SELECT COUNT(*) AS total
FROM employees
WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:

total
3

In this example, we will return the number of employees who have a salary above $50,000. We've aliased the COUNT(*) as total to make our query results more readable. Now, total will display as the column heading when the result set is returned.

Example - Using GROUP BY with the COUNT Function

In some cases, you will be required to use the GROUP BY clause with the COUNT function. This happens when you have columns listed in the SELECT statement that are not part of the COUNT function. Let's explore this further.

Again, using the employees table populated with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

SELECT dept_id, COUNT(*) AS total
FROM employees
WHERE salary > 50000
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id	total
500	2
501	1

In this example, the COUNT function will return the number of employees that make over $50,000 for each dept_id. Because the dept_id column is not included in the COUNT function, it must be listed in the GROUP BY clause.

Example - Using DISTINCT with the COUNT Function

Did you know that you can use the DISTINCT clause within the COUNT function? This allows you to count only the unique values.

Using the same employees table as the previous example:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

SELECT COUNT(DISTINCT dept_id) AS total
FROM employees
WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:

total
2

In this example, the COUNT function will return the unique number of dept_id values that have at least one employee that makes over $50,000.

TIP: Performance Tuning with the COUNT Function

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the parentheses), you can use COUNT(1) to get better performance. Now the database engine will not have to fetch any data fields, instead it will just retrieve the integer value of 1.

For example, instead of entering this statement:

SELECT dept_id, COUNT(*) AS total
FROM employees
WHERE salary > 50000
GROUP BY dept_id;

You could replace COUNT(*) with COUNT(1) to get better performance:

SELECT dept_id, COUNT(1) AS total
FROM employees
WHERE salary > 50000
GROUP BY dept_id;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Practice Exercises

If you want to test your skills using the SQL COUNT function, try some of our practice exercises.

These exercises allow you to try out your skills with the COUNT function. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!