Logo 
Search:

SQL Server Articles

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

GROUPING Function

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

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

Grouping is used to distinguish the null values returned by CUBE and ROLLUP from standard null values. The NULL returned as the result of a CUBE or ROLLUP operation is a special use of NULL. It acts as a column placeholder in the result set and means "all."

It adds an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator or 0 when the row is not the result of CUBE or ROLLUP.

Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

 


Syntax of GROUPING Function :

GROUPING (column_name )

column_name is a column in a GROUP BY clause to check for CUBE or ROLLUP null values.

It returns an integer value.

 


Example of GROUPING Function :

Example 1 : Using GROUPING function in a SELECT clause with GROUP BY clause

SELECT ProductName, SUM(Quantity) AS ‘Quantity’, GROUPING(ProductName) AS ‘Grouping                                       Value’

FROM   Invoices

GROUP  BY ProductName WITH ROLLUP

 

Output

ProductName           Quantity      Grouping Value

Alice Mutton                 978               0

Aniseed Syrup               328                0

Boston Crab Meat       1103               0

Camembert Pierrot     1577               0

Carnarvon Tigers          539                0

NULL                          4525                1


Above example groups productname and summation of quantity. The GROUPING function is applied to the productname column. Last row hahing NULL value in productname field in the summary row added by the ROLLUP operation. The summary row shows the total quantity for all productname groups and is indicated by 1 in the grouping value column.

  
Share: 

 
 
 

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

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

 
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!