Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Aggregate FunctionsRSS Feeds

COUNT_BIG Function

Posted By: Sarita Patel     Category: SQL Server     Views: 7463

This article explains COUNT_BIG function of sql server with different examples.

COUNT_BIG function is used to count particular item in a group. It returns number of items in a group. It works same as COUNT function. The difference between COUNT and COUNT_BIG function is COUNT function returns integer and COUNT_BIG function returns big integer.    

Note that  :

COUNT_BIG(*) returns the number of items in a group including NULL values and duplicates.

COUNT_BIG (ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT_BIG (DISTINCT expression) evaluates expression for each row in a group and returns the number of unique nonnull values.


 

Syntax of COUNT Function :

COUNT_BIG( { [ ALL | DISTINCT ] expression ] | * } )

ALL is the default. It means all values to be count.

DISTINCT means COUNT returns the number of unique nonnull values.

Expression is an expression of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted.

* means all rows should be counted to return the total number of rows in a table. COUNT_BIG (*) takes no parameters and cannot be used with DISTINCT. COUNT_BIG (*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT_BIG (*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.

It returns an integer.



Examples of COUNT_BIG Funtion :

Example 1 : Using COUNT_BIG function in SELECT clause to get total records in a table

SELECT COUNT_BIG(*)

FROM   Products

Output
76

 

Above example displays the total number of records in a product table using count function. It considers all values in a group including null and duplicates.

 


Example 2 : Using COUNT_BIG function in SELECT clause to get total product in invoices table

SELECT COUNT_BIG(ProductName)

FROM   Invoices

Output
2155


Above example displays the total number of product in a invoices table using count function. It considers all values in a group including duplicate values.

 


Example 3 : Using COUNT function and DISTINCT in SELECT clause to get unique products in invoices table

SELECT COUNT_BIG(DISTINCT ProductName)

FROM   Invoices

Output
77


Above example displays the total number of unique product in a invoices table using count function. It considers all values in a group which has unique values.

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on COUNT_BIG Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of COUNT_BIG Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!