SQL COUNT Function

The SQL COUNT is an aggregate function used to return the number of rows that satisfy specified criteria.

Syntax

The basic syntax for the COUNT function is as follows:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Or the syntax for the COUNT function when used with the GROUP BY clause:

SELECT colunm1_name, column2_name, ... columnN_name, COUNT(column_name)
FROM table_name
WHERE condition
GROUP BY column1_name, column2_name, ... columnN_name

Parameters:

• colunm1_name, column2_name, ... columnN_name: Columns that are not encapsulated within the COUNT function and must be included in the GROUP BY clause.

• column_name: This is the column from whose non-NULL values will be counted.

Demo Table

Let us suppose that we have a table called "Employee" with the following data:

COUNT Only includes NOT NULL Values - Example

The COUNT function will return only the number of rows where the column value is NOT NULL in COUNT(column_name). When the column's value is a NULL value, it will not be included in the count calculation.

The following SQL statement will return the number of rows in the "Employee" table:

SELECT COUNT(emp_no)
FROM employee;

After the execution, the output will be as follows:

+---------------+
| COUNT(emp_no) |
+---------------+
| 8             |
+---------------+

As you can see, the query had returned 8 since there are 8 rows in the "Employee" table and all "emp_no" values are NOT NULL.

We will now see what the COUNT function will return if it encounters a NULL value.

SELECT COUNT(dept_name)
FROM employee;

After the execution, you will see the following output:

+------------------+
| COUNT(dept_name) |
+------------------+
| 7                |
+------------------+

This time, the COUNT function returns 7 because one of the "dept_name" values is NULL. It would be excluded from the COUNT function calculation.

Note: Try to use the primary key or the wildcard * in the COUNT function if you want to be sure that rows are not excluded in the calculations.

COUNT With a Single Condition - Example

The following SQL statement will return the number of employees who have a salary above 8300.

SELECT COUNT(*)
FROM employee
WHERE salary > 8300

After running the above SQL statement, the output will be as follows:

+----------+
| COUNT(*) |
+----------+
| 4        |
+----------+

COUNT With GROUP BY clause - Example

It is more often in real-life situations you will need to use the COUNT function with the GROUP BY clause. This happens when you have columns listed in the SELECT statement that are not encapsulated in the COUNT function.

The following SQL statement will return the number of employees for each department.

SELECT dept_name AS 'Department', COUNT(*) AS 'Number of employees'
FROM employee
GROUP BY dept_name;

After the execution, the result set will look as follows:

+------------------+---------------------+
| Department       | Number of employees |
+------------------+---------------------+
| Customer Service | 1                   |
| Development      | 2                   |
| Finance          | 2                   |
| Human Resources  | 1                   |
| Marketing        | 1                   |
+------------------+---------------------+

Note: Every column listed in the SELECT statement and not encapsulated in the COUNT function must be listed in the GROUP BY clause.

COUNT With DISTINCT clause - Example

You can use the DISTINCT clause with the COUNT function to return only the unique values.

The following SQL statement will return the number of "dept_name" values.

SELECT COUNT(DISTINCT dept_name) AS 'Number of department'
FROM employee;

The output of the above SQL statement will be as follows:

+----------------------+
| Number of department |
+----------------------+
| 5                    |
+----------------------+