SQL Server – How to use grouping sets with duplicate null columns
Reading through the 70-461 training kit, and on page 155 the example of grouping sets shows the following query
Select shipperid, year(shipdate), count(*) from sales.orders group by grouping sets ( (shipperid,YEAR(shipdate)), (shipperid), (year(shipdate)) () )
this returns a data set that includes the following two rows:
shipperid shipyear numorders NULL NULL 21 (...) NULL NULL 830
I understand why this happens (because of the different sets defined for the query), but would like to know how this could be used when the two values have nothing to distinguish them?
One Solution collect form web for “SQL Server – How to use grouping sets with duplicate null columns”
As is, you wouldn’t be able to distinguish them, however page 158 goes on to explain the use of the GROUPING_ID function which can be used to distinguish the groups.