SQL COUNT

 

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.

employees_table

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;
SQL COUNT with GROUP BY example

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 INNER JOIN example

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 ORDER BY example

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 with HAVING clause example

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 as the following statement:

SELECT COUNT(job_id) FROM employees;
SQL COUNT expression example

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:

SQL COUNT DISTINCT example

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

SELECT COUNT(DISTINCT manager_id) FROM employees;
SQL COUNT DISTINCT manager example

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.

Reactions

Post a Comment

0 Comments