SQL COUNT

Summary: in this tutorial, you will learn 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);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

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

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: JavaScript (javascript)

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(*)
FROM
	employees
GROUP BY
	department_id;Code language: SQL (Structured Query Language) (sql)

Try It

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

Try It

SQL COUNT(*) with ORDER BY clause example

The following example uses the ORDER BY clause to sort the number of employees by department:

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

Try It

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

Try It

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

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;

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

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.