SQL COUNT_BIG function

The SQL COUNT_BIG is one of the SQL Aggregate Function which is used to Count the number of items / rows selected by the SELECT Statement. It works same as the SQL Count function, but it returns the bigint. In this article we will show you, How to write COUNT_BIG in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL COUNT_BIG (*) Example

The SQL Server COUNT_BIG (*) simply returns the total number of records present in the MyEmployees table. Let us see one example for better understanding

T-SQL CODE

-- SQL Server COUNT_BIG Function Example
USE [SQL Tutorial]
GO
SELECT COUNT_BIG(*) AS [Number of Employees]
FROM [MyEmployees]

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

OUTPUT

SQL COUNT_BIG (Column Name) Example

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

T-SQL CODE

-- SQL Server COUNT_BIG Function Example
USE [SQL Tutorial]
GO
SELECT COUNT_BIG([EmployeeID]) AS [Number of Employees]
      ,COUNT_BIG([ManagerID]) AS [Number of Managers]	
FROM [MyEmployees]

OUTPUT

SQL Unique Count BIG Example

The SQL COUNT_BIG (DISTINCT Column Name) returns the Unique number of records present in the specified column whose values are NOT NULL.

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

T-SQL CODE

-- SQL Server COUNT_BIG Function Example
USE [SQL Tutorial]
GO
SELECT COUNT_BIG(DISTINCT [EmployeeID]) AS [Number of Unique Employees]
      ,COUNT_BIG(DISTINCT [ManagerID]) AS [Number of Unique Managers]	
FROM [MyEmployees]

OUTPUT

SQL COUNT BIG with GROUP BY Clause

In general, we write SQL query to 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_BIG Function to count the number of products present in that group. Let us see the Example

T-SQL CODE

-- SQL Server COUNT_BIG Function Example
USE [SQL Tutorial]
GO
SELECT COUNT_BIG( [EmployeeID]) AS [Number of Employees]
	  ,[Education]
      ,SUM([YearlyIncome]) AS [Total Income]
      ,SUM([Sales]) AS [Total Sale]
 FROM [MyEmployees]
 GROUP BY [Education]
 ORDER BY [Total Income] DESC

OUTPUT

Thank You for Visiting Our Blog