SQL COUNT Function

The SQL Server COUNT Function is one of the Aggregate Function which is used to Count the number of records (or rows) selected by the SELECT Statement.

For example, If you want to find the total Number of products present in your Store or you want to find the Black colour products present in your Store. In these situations, you can simply use this COUNT in SQL Server.

SQL Server Count Syntax

The basic syntax of the Count in SQL Server is as shown below:

SELECT COUNT([Column_Name])
FROM [Source]

In this article, we will show you, How to write COUNT in SQL Server with an example. For this, We are going to use the below-shown data

SQL COUNT(*) Example

The COUNT(*) simply returns the total number of records present in the table. Let us see one example to understand it better

-- SQL Server Count Example
USE [SQLTEST]
GO
SELECT COUNT(*) AS [Total Records]
  FROM [Employees]

Above SQL Query will count the total number of records present in the Employee table

OUTPUT

NOTE: The COUNT(*) counts NULL values and Duplicate records as well.

SQL COUNT (Column Name) Example

The COUNT(Column Name) simply returns the total number of records present in the table whose values are NOT NULL (Ignores the NULL Records). Let us see one example

NOTE: The COUNT(Column Name) counts the Duplicate records.

-- SQL Server Count Example
SELECT COUNT([FirstName]) AS [Total Records]
  FROM [Employees]

OUTPUT

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

-- SQL Server Count Example
SELECT COUNT(DeptID) AS [Total Records]
  FROM [Employees]

Above SQL Query will count the total number of records present in the [DepartID] whose values are NOT NULL

OUTPUT

SQL Unique Count Example

The COUNT(DISTINCT Column_Name) simply returns the Unique number of records 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.

-- SQL Server Count Example
SELECT COUNT(DeptID) AS [Total Records],
	COUNT(DISTINCT DeptID) AS [Distinct Records]
  FROM [Employees]

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

OUTPUT

SQL Distinct Count Example 2

This example shows you the difference between the Normal Count and the Count Distinct. For better understand, we selected multiple columns with Count and Count Distinct separately

-- SQL Server Count Example
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]

OUTPUT

SQL ALL Count Example

The COUNT(ALL Column_Name) is same as COUNT(Column_Name). The following example shows you the same.

-- SQL Server Count Example
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]

OUTPUT

SQL Count With Where Clause Example

You can use Count function along with Where Clause. In this example, we are using where clause to count the total number of records whose yearly income is greater than or equal to 60000

-- SQL Server Count Example
SELECT COUNT(*) AS [Total Records]
  FROM [Employees]
  WHERE YearlyIncome >= 60000

OUTPUT

SQL Count IS NULL Example

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

-- SQL Server Count Example
SELECT COUNT(*) AS [Total Records]
  FROM [Employees]
  WHERE DeptID IS NULL

OUTPUT

SQL Count IS NOT NULL Example

In this example, we used IS NOT NULL to count the total customers whose Department ID is not Null

-- SQL Server Count Example
SELECT COUNT(*) AS [Total Records]
  FROM [Employees]
  WHERE DeptID IS NOT NULL

OUTPUT

SQL COUNT with GROUP BY Clause

In most cases, we usually count the number of customers belongs to a particular region etc. In these situations, we use GROUP BY clause to group the customers by region, profession, education, state etc. Next, we use the COUNT Function to count the number of customers present in that group. Let us see an Example

-- SQL Server Count Function Example
SELECT Education,
	COUNT(*) AS [Total Records]
  FROM [Employees]
  GROUP BY Education

Above SQL Query will find the total number of Employees belongs to each education group.

OUTPUT

GROUP BY With Count in SQL Server Example 2

This is another example to showcase the Group By clause along with the Count Function. In this example, we are using Count, Count Distinct functions on multiple columns along with the group by.

-- SQL Server Count Function Example
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

OUTPUT

SQL COUNT with HAVING Clause

If you want to check the conditions against the aggregated data then you have to use HAVING Clause along with Group By Statement.

For example, the following query will group the Customers by their Education and counts the records present in each group.

-- SQL Server Count Function Example
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

Below lines of code will check whether the total count of Employee Ids is greater than 1 or not. If this is True then corresponding records will be displayed.

HAVING COUNT(EmpID) > 1

OUTPUT

SQL COUNT in ORDER BY Clause

In SQL Server, you can use the Count function in the Order By Clause. Below query count the records group by the Education. Next, Order By clause will sort those query result based on the Distinct Count of first name result in descending order.

-- SQL Server Count Function Example
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

OUTPUT

Use Joins along with Count in SQL Server

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

-- SQL Server Count Function Example
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

OUTPUT

Use Joins with Count in SQL Server Example 2

This is another example to demonstrate the Joins along with count function.

-- SQL Server Count Function Example
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

OUTPUT

SQL COUNT Example – Multiple tables

You can use Count function on Multiple Tables. Below query will count all the records from Employee table, and Department table.

-- SQL Server Count Function Example
USE [SQLTEST]
GO
SELECT 
	(SELECT COUNT(*) 
		FROM [Employees]
	) AS [Total Employees],
	(SELECT COUNT(*) 
		FROM [Department]
	) AS [Total Departments]

OUTPUT