SQL COUNT

Summary: This tutorial shows you how to use the SQL COUNT function to get the number of items in a group.

Introduction to SQL COUNT function

The SQL COUNT function is an aggregate function that returns the number of rows returned by a query. You can use the COUNT function in the SELECT statement to get the number of employees, the number of employees in each department, the number of employees who hold a specific job, etc.

The following illustrates the syntax of the SQL COUNT function:

COUNT([ALL | DISTINCT] expression);

The result of the COUNT function depends on the argument that you pass to it.

• By default, the COUNT function uses the ALL keyword whether you specify it or not. The ALL keyword means that all items in the group are considered including the duplicate values. For example, if you have a group (1, 2, 3, 3, 4, 4) and apply the COUNT function, the result is 6.
• If you specify the DISTINCT keyword explicitly, only unique non-null values are considered. The COUNT function returns 4 if you apply it to the group (1,2,3,3,4,4).

Another form of the COUNT function that accepts an asterisk (*) as the argument is as follows:

COUNT(*)

The COUNT(*) function returns the number of rows in a table including the rows that contain the NULL values.

SQL COUNT function examples

Let’s take some examples to see how the COUNT function works. We will use the employees the table in the sample database for demonstration purposes.

SQL COUNT(*) example

To get the number of rows in the employee's table, you use the COUNT(*) function table as follows:

SELECT 
    COUNT(*)
FROM
    employees;

To find how many employees work in the department id 6, you add the WHERE clause to the query as follows:

SELECT 
    COUNT(*)
FROM
    employees
WHERE
    department_id = 6;

Similarly, to query the number of employees whose job id is 9, you use the following statement:

SELECT 
    COUNT(*)
FROM
    employees
WHERE
    job_id = 9;

SQL COUNT with GROUP BY clause example

To find the number of employees per department, you use the COUNT with GROUP BY clause as follows:

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

To get the department name in the result set, we need to use the inner join to join the employee's table with the department's table as follows:

SELECT
	e.department_id,
	department_name,
	COUNT(*)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id;

SQL COUNT(*) with ORDER BY clause example

You can use the COUNT(*) function in the ORDER BY clause to sort the number of rows per group. For example, the following statement gets the number of employees for each department and sorts the result set based on the number of employees in descending order.

SELECT
	e.department_id,
	department_name,
	COUNT(*)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
ORDER BY
	COUNT(*) DESC;

SQL COUNT with HAVING clause example

To filter the groups by the result of the COUNT(*) function, we need to use the COUNT(*) function in the HAVING clause.

For example, the following statement gets the departments and their number of employees. In addition, it selects only departments whose number of employees is greater than 5.

SELECT
	e.department_id,
	department_name,
	COUNT(*)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
HAVING
	COUNT(*) > 5
ORDER BY
	COUNT(*) DESC;

SQL COUNT(DISTINCT expression) example

To get the number of jobs in the employees table, you apply the COUNT function to the job_id column like the following statement:

SELECT 
    COUNT(job_id)
FROM
    employees;

The query returns 40 that includes the duplicate job id. We expected to find the number of jobs that are holding by employees.

To remove the duplicate, we add the DISTINCT keyword to the COUNT function as follows:

You can use the COUNT DISTINCT to get the number of managers as the following query:

SELECT 
    COUNT(DISTINCT manager_id)
FROM
    employees;

Note that the president does not have a manager.

In this tutorial, you have learned the various ways to apply the SQL COUNT function to get the number of rows in a group.