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.