Mssql count sub select in the where clause

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:

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

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:

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

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 table in the sample database for the demonstration purposes.

SQL COUNT (*) example

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

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

See it in action

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

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

See it in action

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

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

See it in action

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:

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

See it in action

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

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

See it in action

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.

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

See it in action

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.

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

See it in action

SQL COUNT (DISTINCT expression) example

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

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

See it in action

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:

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

See it in action

Note that the president does not have the 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.