GROUPING and GROUPING_ID Functions in SQL Server(TSQL)

 

We can create subtotal and grandtotal rows for the grouped data with WITH ROLLUP, WITH CUBE and GROUPING SETS operators. You can find details in the following articles.

WITH ROLLUP and WITH CUBE Operators in SQL Server(TSQL)“,

GROUPING SETS Operator in SQL SERVER(TSQL)

When you use the WITH ROLLUP and WITH CUBE operators, as you can see in the screenshot above, aggregated columns appear to be null. But because the queried data set has NULL values, some columns may be NULL.

GROUPING Function:

We use the GROUPING function to separate these two NULL types. Using the GROUPING function, we can ensure that the results are more meaningful when we use the ROLLUP and CUBE operators.

If we continue with the example in the “WITH ROLLUP and WITH CUBE Operators in SQL Server(TSQL)” article;

The first script was written without using the GROUPING function.

We set another value for records that appear to be NULL because they are aggregated with the second script.

 

If we do the same thing for ProductCode column we will get the error. Because ProductCode column’s data type is int. That’s why, we can not set string data for the int column.

Msg 245, Level 16, State 1, Line 5

Conversion failed when converting the varchar value ‘Product Code Groups’ to data type int.

But if you want you can add a column to determine the product code’s null type as follows. If the column is null because the results are aggregated, GP_ProductCode will be 1. If not it will¬† be 0.

GROUPING_ID Function:

The GROUPING_ID function evaluates the values of the columns based on the Base2 number system and returns the corresponding int value as a result.

The following table I got from msdn has explained the difference between GROUPING_ID function and GROUPING function very well.

Columns aggregated

GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)

GROUPING_ID () output

a

100

4

b

010

2

c

001

1

ab

110

6

ac

101

5

bc

011

3

abc

111

7

In the first query below, we used the GROUPING function for the ProductCode and ProductName columns.

In the second query, we specified these two columns in the GROUPING_ID function. When you interpret the results according to the table above, you will understand the GROUPING_ID function better.

Review of the screenshot above:

GP_ProductCode

GP_ProductName

The combination of two columns:

GP_ProductCode_and_ProductName

0

0

00

0

0

1

01

1

0

0

00

0

0

1

01

1

0

0

00

0

0

1

01

1

1

1

11

3

1

0

10

2

1

0

10

2

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *