A Quick Binary Refresher for Analytic Query Writers

You probably thought that the chapter on binary number systems in your Intro to Computer Science textbook would never have to be opened again, especially now that you’re a report writer or a data analyst and generally don’t get too close to the silicon. SQL Server DBAs bump into binary numbers from time to time when using some administrative and monitoring functions. But even analysts may have to deal with them when writing some queries that involve aggregation and grouping.

CUBE and ROLLUP

According to the strict rules, query results should contain detail data or aggregate data, but not both. CUBE and ROLLUP violate the strict rules by not only mixing details with aggregated values, but also by included values aggregated at different levels.

In this example, the NULL at row 8 indicates the row value for Argentina is aggregated over all beverages; the NULL at row 19 represents the beverage aggregation for Austria.

Clearly “NULL” is hardly acceptable for a report to be viewed by anyone other than a hard-core analyst.. Even to this day, the classic GROUPING( ) function is the simplest and most practical means to solve this problem. GROUPING(columnName) will return 1 if that particular row represents a grouping on the “columnName” column. In the following example we use GROUPING( ) to improve the readability of the results.

SELECT CASE GROUPING(Productname)

WHEN 1 THEN ‘All Products’

ELSE Productname

END AS ProductName

,CASE GROUPING(ShipCountry)

WHEN 1 THEN ‘All Countries’

ELSE ShipCountry

END AS ShipCountry

,CASE GROUPING(CategoryName)

WHEN 1 THEN ‘All Categories’

ELSE CategoryName

END AS CategoryName

,SUM(CAST(od.UnitPrice * od.Quantity * (1 od.Discount)AS MONEY)) AS Total

FROM [Order Details] od

JOIN Products p

ON p.ProductID = od.ProductID

Join Orders o

ON od.OrderID = o.OrderID

JOIN Categories c

ON p.CategoryID = c.CategoryID

GROUP BY CUBE(ProductName, ShipCountry, CategoryName)

GROUPING_ID( )

The GROUPING_ID( ) function provides an alternative for managing group labels in the output. In the following example, a new column is created to provide a description of which column or combination of columns is being grouped for any particular output row.

SELECT CASE GROUPING_ID(ProductName, ShipCountry, CategoryName)

WHEN 1 THEN ‘Categories’

WHEN 2 THEN ‘ShipCountries’

WHEN 3 THEN ‘Categories, ShipCountries’

WHEN 4 THEN ‘Products’

WHEN 5 THEN ‘Products, Categories’

WHEN 6 THEN ‘Products, ShipCountries’

WHEN 7 THEN ‘Products, ShipCountries, Categories’

END

AS GroupingLevel

,Productname

,ShipCountry

,CategoryName

,SUM(CAST(od.UnitPrice * od.Quantity * (1 od.Discount)AS MONEY)) AS Total

FROM [Order Details] od

JOIN Products p

ON p.ProductID = od.ProductID

Join Orders o

ON od.OrderID = o.OrderID

JOIN Categories c

ON p.CategoryID = c.CategoryID

GROUP BY CUBE(ProductName, ShipCountry, CategoryName)

This is all very well and good, but where did the 1, 2, 3, …, 7 come from in the CASE statement in the query? This is where the binary comes into play.

If we call GROUPING_ID with a single column argument, the results are indistinguishable from those of GROUPING. However, if we invoke GROUPING_ID with a set of column names as an argument, each column name becomes associated with a single bit in the output. For example, in the above query we used

GROUPING(ProductName, ShipCountry, CategoryName)

The output will be a single integer, but let’s consider it as a row of bits.

ProductName

ShipCountry

CategoryName

24

23

22

21

20

16

8

4

2

1

The least-order bit is associated with the last column in the list, the second-least order bit is associated with the second-to-last column and so forth. Note that bits are determined by the argument list to the GROUPING_ID function and have nothing to do with the sequence of column names in the GROUP BY or CUBE function.

In this example, if a row represented a grouping on only ProductName, then the value in the ProductName “column” would be 1, representing a bit in the “4s” column. Since ShipCountry and CategoryName are zero, the total for the GROUPING_ID output would be 4.

ProductName

ShipCountry

CategoryName

24

23

22

21

20

16

8

4

2

1

0

0

1

0

0

Similarly, if the row represented an grouping on ProductName and ShipCountry, but not on CategoryName, there would be a “1” bit in the 4s and the 2s column.

ProductName

ShipCountry

CategoryName

24

23

22

21

20

16

8

4

2

1

0

0

1

1

0

The total value for the output integer in this case would be 6.

Conclusion

In all likelihood, the GROUPING( ) function will continue to serve most of your needs for queries using CUBE and ROLLUP. However, if you need more flexibility, GROUPING_ID is easy to use once you see how the values for the integer output are calculated and interpreted.

Related Training:
Course 234
Course 929
Course 8420
SQL Server Training

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.