SQL COUNT Aggregate Function

Summary: in this tutorial, you will learn how to use the SQL COUNT function to get the number of rows in a table.

Introduction to SQL COUNT function #

The SQL COUNT function is an aggregate function that returns the number of rows returned by a query.

For example, 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);Code language: SQL (Structured Query Language) (sql)

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

The ALL keyword will include the duplicate values in the result. For example, if you have a group (1, 2, 3, 3, 4, 4) and apply the COUNT function, the result is 6. By default, the COUNT function uses the ALL keyword whether you specify it or not.
The DISTINCT keyword counts only unique values. For example, the COUNT function returns 4 if you apply it to the group (1, 2, 3, 3, 4, 4).
The expression is a column of a table where you want to count the value.

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

COUNT(*)Code language: SQL (Structured Query Language) (sql)

The COUNT(*) function returns the number of rows in a table in a query. It counts duplicate rows and rows that contain null values.

SQL COUNT function examples #

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

SQL COUNT(*) examples #

The following example uses the COUNT(*) function to get the number of rows from the employees table:

SELECT
  COUNT(*)
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 count
-------
    40

The following example uses the COUNT(*) function to count employees who work in the department with id 6:

SELECT
  COUNT(*)
FROM
  employees
WHERE
  department_id = 6;Code language: SQL (Structured Query Language) (sql)

Try it

In this example:

First, the WHERE clause filter department with id 6.
Second, the COUNT(*) function returns the number of rows from the employees table with the value in the department id 6.

The following example uses the COUNT(*) function to get the number of employees with the job id 9:

SELECT
  COUNT(*)
FROM
  employees
WHERE
  job_id = 9;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 count
-------
     5

How it works.

First, the WHERE clause includes the rows from the employees table with the job id 9.
Second, the COUNT(*) returns the number of rows from the employees table with the job id 9

The following example uses the AS keyword to assign the COUNT(*) a column alias:

SELECT
  COUNT(*) AS employee_count
FROM
  employees
WHERE
  job_id = 9;Code language: PHP (php)

Try it

Output:

 employee_count
----------------
              5

SQL COUNT with GROUP BY clause examples #

The following example uses the COUNT function with GROUP BY clause to find the number of employees for each department:

SELECT
  department_id,
  COUNT(*) employee_count
FROM
  employees
GROUP BY
  department_id
ORDER BY
  employee_count;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_id | employee_count
---------------+----------------
             7 |              1
             4 |              1
             1 |              1
            11 |              2
             2 |              2
             9 |              3
             6 |              5
             8 |              6
             3 |              6
            10 |              6
             5 |              7

How it works:

First, the GROUP BY clause groups the rows in the employees table by the department id.
Second, the COUNT(*) function returns the number of rows for each group

The following example uses the COUNT(*) function to get the number of employees by department. Also, it uses an INNER JOIN clause to include the department name in the result set:

SELECT
  department_name,
  COUNT(*) employee_count
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
ORDER BY
  employee_count;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name  | employee_count
------------------+----------------
 Administration   |              1
 Human Resources  |              1
 Public Relations |              1
 Accounting       |              2
 Marketing        |              2
 Executive        |              3
 IT               |              5
 Finance          |              6
 Purchasing       |              6
 Sales            |              6
 Shipping         |              7Code language: PHP (php)

SQL COUNT with HAVING clause example #

The following example returns the number of employees by department. Also, it uses a HAVING clause to select only departments that have more than five employees:

SELECT
  department_name,
  COUNT(*) employee_count
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  COUNT(*) > 5
ORDER BY
  employee_count DESC;Code language: SQL (Structured Query Language) (sql)

Try it

 department_name | employee_count
-----------------+----------------
 Shipping        |              7
 Purchasing      |              6
 Finance         |              6
 Sales           |              6

SQL COUNT(DISTINCT expression) example #

The following example uses the COUNT to get the number of values in the job_id column in the employees table:

SELECT
  COUNT(job_id)
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

 count
-------
    40

The query returns 40 which includes the duplicate values. To remove the duplicates, you use the DISTINCT keyword to the COUNT function as follows:

SELECT
  COUNT(DISTINCT job_id)
FROM
  employees;

Try it

 count
-------
    19

The following example uses the COUNT function with the DISTINCT keyword to get the number of managers:

SELECT
  COUNT(DISTINCT manager_id)
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 count
-------
    10

Summary #

The COUNT function returns the number of rows in a group.
The ALL keyword includes duplicate values while the DISTINCT keyword removes the duplicate values in the result.
The COUNT(*) returns the number of rows in a query including duplicate rows and rows that contain null values.

Databases #

Was this tutorial helpful ?