SQL Server GROUPING (Transact-SQL)

Hello Folks,

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 SQL Server 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 “dbo.Students” in the database “TEST”. So this can be seen as:

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

SELECT GROUPING(Class) AS 'Grouping',
Class,
SUM(Salary) AS Amount
FROM Students
GROUP BY ROLLUP(Class);

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. The error would be somewhat related to-

For e.g., let assume that if you want to add more attributes to the SELECT statement, then you might be getting an error:

Msg 8120, Level 16, State 1, Line 1

Column ‘Students.S_Id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If you are really suffering this error, then I would suggest you to review my blog post about it. Here is the link;

This was all about GROUPING function in T-SQL.

Hope you like it 🙂

Have a wonderful day!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook