totn SQL

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 techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Enter the following SELECT statement that uses the COUNT function:

Try It
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:

Try It
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:

Try It
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:

Try It
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:

Try It
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:

Try It
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:

Try It
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!

Go to Practice Exercises