SQL Count Function – How To Use Count in SQL

Summary: This tutorial explains how to use the SQL COUNT function with syntax, examples, and also you will learn how to get the number of items in a set with the function count in SQL.

Introduction to SQL COUNT function

The SQL COUNT function returns the number of rows in a table satisfying the criteria described in the WHERE clause. It sets the non NULL column values or the number of rows. COUNT() returns zero if there were no matching rows. You’ll use the SQL server COUNT function in the SQL SELECT count statement such as to get the no. of employees, the no. of employees in every department, and the no. of employees who hold a particular job, etc.

Syntax:

The following illustrates the syntax of the COUNT function:

Therefore, the above general syntax is SQL 2003 ANSI standard syntax. this helps us to understand the way COUNT() function is used. but you can see that different database vendors may have different ways of applying the COUNT() function.
The result of the COUNT function in SQL depends on the argument that you pass to it.

• Overall, you can use * or ALL or DISTINCT or some expression along with COUNT to COUNT the number of rows w.r.t. some condition or all of the rows, depending upon the arguments you are using along with the COUNT() function.
• By default, the function COUNT in SQL uses the ALL keyword whether you specify it or not. The means of ALL keyword is that all items in the group are considered including the duplicate values. For example, if you have a group (1, 4, 5, 5, 6, 6, 7) and apply the COUNT function, the result is 7.
• Therefore, If you specify the DISTINCT keyword explicitly, only unique non-null values are considered. The COUNT function returns 5 if you apply it to the group (1, 4, 5, 5, 6, 6, 7).

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

DBMS Support: SQL COUNT Function

DB2 and Oracle Syntax:

Parameters:

SQL COUNT Function Examples

There is the given below the following example, an asterisk character ( * ) is used followed by the COUNT() SQL means which indicates all the rows of the table even if there is any NULL value.
Let’s see a few examples of how the COUNT function works. We will use the employees table in the sample database for demonstration purposes.

Sample Table: Orders

SQL COUNT(*) Example

In this example, use the COUNT(*) function, to get the number of rows in the employees table, as follows:

SELECT 
    COUNT(*)
FROM
    Employees;

Output:

10

Similarly, to find the number of employees whose job id is 2, therefore, you use the following statement:

SELECT 
    COUNT(*)
FROM
    Employees
WHERE
    Job_Id = 2;

Output:

5

Furthermore, let’s find how many employees who work in the department id 7, you need to add the SQL WHERE clause to the query as follows:

SELECT 
    COUNT(*)
FROM
    Employees
WHERE
    Department_Id = 7;

Output:

4

SQL COUNT With GROUP BY Clause Example

In this example Group BY Clause, we find the number of employees per department, here we use the COUNT with GROUP BY clause as follows:

SELECT
	Department_Id,
	COUNT(*)
FROM
	Employees
GROUP BY
	Department_Id;

Output: