SQL Server GROUPING (Transact-SQL)

Hello Friends,
You may have heard about this function or maybe not. It’s a very interesting function, which you should also know that. Well don’t worry at all I am going to give a brief about GROUPING function, so watch it carefully:

• The GROUPING function was introduced earlier in the SQL Server 2000, but later on there has always been the modification in it, adds more perfection to it. 
• It Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
• It returns 1 for aggregated or 0 for not aggregated in the result set.
• Grouping can also be defined as an aggregate function that causes 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.
• It can be used only in the SELECT list, HAVING, and ORDER BY clauses when GROUP BY is specified.
• The syntax can be seen as:

GROUPING ( <column_expression> )
Here, the “column_expression” tells about column or an expression that contains a column in a GROUP BY clause.

• The return type for the GROUPING function is tinyint.
• GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values.
• The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all.
• This will become more clear to you after you see this example:

I have created the table “rms.payment” in the database “TEST”. So this can be seen as:

 

Now, if we want to see that how much “Amount” is being summed up for each “bank_name” and so the status of the “Grouping”, till when it’s 0 or 1, then see this query carefully:

SELECT GROUPING(bank_name) AS 'Grouping',bank_name,SUM(amount) AS Amount
FROM [test].rms.payment
GROUP BY rollup (bank_name)

The result can be seen as: 

 

As you can see that as soon as the status of the Grouping becomes 1, a column placeholder comes into the act (special NULL) and which means all. If you don’t want a NULL into your result set, so it can also be replaced by any string with the use of WHEN clause or any other clause. The “Amount” column also sums up all the groups, and then displays the result.
Note: It might also possible that you might get an error while using the SELECT statement.