SQL COUNT Function

The SQL Server COUNT Function is one of the Aggregate Functions which Counts the total number of rows (records) selected by the SELECT Statement

For example, If you require to get the total number of products in your Store. Or you want to locate the Black color products present in your Store. In these situations, you can use this SQL COUNT function, and the basic syntax of it is as shown below:

SELECT COUNT([Column_Name])
FROM [Source]

For this SQL Count function demonstration, we are going to use the below-shown data

Employee table for Aggregate Function Example 1

SQL COUNT (*) Example

The SQL Server COUNT * returns the number of records from the employee’s table. Let us see an example to understand this Aggregate Function better.

SELECT COUNT(*) AS [Total Records]
  FROM [Employees]

The above Select Statement query finds the total records present in the Employee table.

NOTE: The SQL COUNT(*) adds up NULL values and Duplicate records, as well. And the (Column Name) considers and adds the Duplicate records.

SQL COUNT (Column Name) Example

It returns the total number of employees present in the table whose values are NOT NULL (Ignores the NULL Records). Let us see one example of this function.

SELECT COUNT([FirstName]) AS [Total Records]
  FROM [Employees]

We got the same result as the COUNT(*) query. Because there are no NULL Values in the [FirstName] Column. Let us try another Column, i.e., DeptID.

SELECT COUNT(DeptID) AS [Total Records]
  FROM [Employees]

The above query counts the total records in the [DepartID] whose values are NOT NULL

SQL Count Distinct Values Example

The DISTINCT Column_Name returns the Unique number of rows present in the table whose values are NOT NULL (Ignores the NULL Records).

TIP: The DISTINCT Keyword is used to remove the Duplicates from the specified column Name.

SELECT COUNT(DeptID) AS [Total Records],
 COUNT(DISTINCT DeptID) AS [Distinct Records]
  FROM [Employees]

Above SQL Server count distinct query will count the Unique number of records (by removing duplicates) present in the [DepartID] whose values are NOT NULL

Return Total Number of Distinct records in a Customer Table 5

Count Distinct Values Example 2

This example shows the difference between the Normal and the select Distinct count. To better understand, we selected multiple columns with normal and Distinct separately.

SELECT COUNT(Education) AS [Total Education],
 COUNT(DISTINCT Education) AS [Distinct Education],
 COUNT(FirstName) AS [Total FirstName],
 COUNT(DISTINCT FirstName) AS [Distinct FirstName],
 COUNT(DeptID) AS [Total Ids],
 COUNT(DISTINCT DeptID) AS [Distinct Ids]
  FROM [Employees]

SQL Server COUNT Distinct Function Employees example 6

SQL Count All Example

It is the same as Column_Name. The following Server example shows the same.

SELECT COUNT(ALL Education) AS [Total Education],
 COUNT(DISTINCT Education) AS [Distinct Education],
 COUNT(ALL FirstName) AS [Total FirstName],
 COUNT(DISTINCT FirstName) AS [Distinct FirstName],
 COUNT(ALL DeptID) AS [Total Ids],
 COUNT(DISTINCT DeptID) AS [Distinct Ids]
  FROM [Employees]

SQL Count Where Clause Example

We can use this function along with the Where Clause. In this example, we are using the where clause to add up the total employees whose yearly income is greater than or equal to 60000

SELECT COUNT(*) AS [Total Records]
  FROM [Employees]
  WHERE YearlyIncome >= 60000

SQL Count IS NULL Example

In this example, we used IS NULL to find the total number of customers whose Department ID is Null.

SELECT COUNT(*) AS [Total Records]
  FROM [Employees]
  WHERE DeptID IS NULL

COUNT IS NOT NULL Example

In this SQL Count function example, we used IS NOT NULL to calculate the total customers whose Department ID is not Null

SELECT COUNT(*) AS [Total Records]
  FROM [Employees]
  WHERE DeptID IS NOT NULL

SQL COUNT GROUP BY Clause Example

In most cases, we usually find the number of customers who belongs to a particular region, etc. In these situations, we use the GROUP BY clause to group the customers by region, profession, education, state, etc. Next, we use this function to find the number of customers present in that group. Let us see an example of it.

SELECT Education,
 COUNT(*) AS [Total Records]
  FROM [Employees]
  GROUP BY Education

The above query finds the total number of Employees belonging to each education group.

It is another example to showcase the Group By clause and this Function. In this example, we use this along with the Distinct functions on multiple columns and the group by clause.

SELECT Education,
 COUNT(FirstName) AS [Total FirstName],
 COUNT(DISTINCT FirstName) AS [Distinct Name],
 COUNT(EmpID) AS [Total Ids],
 COUNT(DeptID) AS [Total DeptIds]
  FROM [Employees]
  GROUP BY Education

SQL COUNT Group By Having Clause

If you want to check the conditions against the aggregated data, then you have to use the HAVING Clause along with Group By Statement. For example, the following query will group the Customers by their Education and add up the records present in each group.

SELECT Education,
 COUNT(FirstName) AS [Total FirstName],
 COUNT(DISTINCT FirstName) AS [Distinct Name],
 COUNT(EmpID) AS [Total Ids],
 COUNT(DeptID) AS [Total DeptIds]
  FROM [Employees]
  GROUP BY Education
  HAVING COUNT(EmpID) > 1

The last line of code will check whether the total number of Employee Ids is greater than one or not. If this is True, then corresponding records will display.

SQL Count function Order By Clause

You can use this count function in Order By Clause. The below query adds up the records group by Education.

Next, Order By Clause will sort those query results based on the Distinct records of a first name result in descending order.

SELECT Education,
 COUNT(FirstName) AS [Total FirstName],
 COUNT(DISTINCT FirstName) AS [Distinct Name],
 COUNT(DeptID) AS [Total DeptIds]
  FROM [Employees]
  GROUP BY Education
  ORDER BY COUNT(DISTINCT FirstName) DESC

Joins Example

You can use this SQL Count function along with the Joins. In this example, we are using Inner Join to join Employee and Departement table. Next, we are computing the total records grouped by Department name.

SELECT E.DeptID,
 dep.DepartmentName,
 COUNT(*) AS [Total Records]
  FROM [Employees] E
  INNER JOIN Department dep ON
  E.DeptID = dep.DeptID
  GROUP BY E.DeptID, dep.DepartmentName

It is another example to demonstrate the Joins along with this SQL Server COUNT function.

SELECT E.Education,
 dep.DepartmentName,
 COUNT(*) AS [Total Records]
  FROM [Employees] E
  INNER JOIN Department dep ON
  E.DeptID = dep.DeptID
  GROUP BY E.Education, dep.DepartmentName
  ORDER BY Education

Multiple tables Example

You can use this Count function on Multiple Tables. The below query will compute all the records from the Employee table and Department table.

-- Multiple Tables Example

SELECT 
 (SELECT COUNT(*) 
 FROM [Employees]
 ) AS [Total Employees],
 (SELECT COUNT(*) 
 FROM [Department]
 ) AS [Total Departments]