SQL COUNT Function

The SQL Server COUNT Function is one of the SQL 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 color products present in your Store. In these situations you can simply use the SQL COUNT function.

In this article we will show you, How to write COUNT Function in In SQL Server with 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

T-SQL CODE

-- SQL Server Count Function example
SELECT COUNT (*) AS [Total Records]
  
 FROM [Employee]

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.

T-SQL CODE

-- SQL Server Count Function example
SELECT COUNT ([FirstName]) AS [Total Records]
  
 FROM [Employee]

OUTPUT

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

T-SQL CODE

-- SQL Server Count Function
SELECT COUNT ([DepartID]) AS [Total Records]
  
 FROM [Employee]

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 SQL DISTINCT Keyword is used to remove the Duplicates from the specified column Name.

T-SQL CODE

-- SQL Server Count Function example
SELECT COUNT (DISTINCT [DepartID]) AS [Total Records]
  
 FROM [Employee]

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

OUTPUT

SQL COUNT with GROUP BY Clause

In most cases we usually check for number of products belongs to particular category or color etc. In these situations we use GROUP BY clause to group the products by color or category and then use the COUNT Function to count the number of products present in that group. Let us see the Example

T-SQL CODE

-- SQL Server Count Function Example
SELECT [DepartID], 
      COUNT ([FirstName]) AS [Total Records]
 FROM [Employee]
 GROUP BY [DepartID]

Above SQL Query will find, How many Employees are associated with the particular Department ID.

OUTPUT

From the above screenshot you can observe that, SQL Query is grouping the NULL records and count them as well. Let us use the WHERE Clause with NOT NULL and check again the result

OUTPUT

TIP: Use NOT NULL to avoid the NULL records in the query

Thank You for Visiting Our Blog