Mục Lục

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)

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)

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

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

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