Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 | SELECT [ProductCode],[ProductName],SUM(ProductPrice) AS SUM_ProductPrice FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH ROLLUP SELECT [ProductCode], (CASE WHEN GROUPING([ProductName])=1 THEN 'ALL Products' ELSE [ProductName] END) AS [ProductName], SUM(ProductPrice) AS SUM_ProductPrice FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH ROLLUP |
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.
1 2 3 4 5 6 7 8 9 10 | SELECT [ProductCode],[ProductName],SUM(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH CUBE SELECT (CASE WHEN GROUPING([ProductCode])=1 THEN 'Product Code Groups' ELSE [ProductCode] END) AS [ProductCode], [ProductName],SUM(ProductPrice) AS SUM_ProductPrice FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH CUBE |
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.
1 2 3 4 | SELECT [ProductCode],[ProductName],SUM(ProductPrice), GROUPING([ProductCode]) AS GP_ProductCode FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH CUBE |
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.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT [ProductCode],[ProductName],SUM(ProductPrice), GROUPING([ProductCode]) AS GP_ProductCode, GROUPING([ProductName]) AS GP_ProductName FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH CUBE SELECT [ProductCode],[ProductName],SUM(ProductPrice), GROUPING_ID([ProductCode],[ProductName]) AS GP_ProductCode_and_ProductName FROM [dbo].[ProductTable] GROUP BY [ProductName],[ProductCode] WITH CUBE |
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 |
