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:

  • SQL Server Group data by week but show the start of the week
  • Most efficient way to read XML in ADO.net from XML type column in SQL server?
  • mySQL for SqlBulkCopy
  • Set RESTRICTED_USER on Azure SQL database
  • Set value of previously declared variables in Dynamic Select in SQL Server
  • Where is SQL Profiler in my SQL Server 2008?
  • 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.

    http://msdn.microsoft.com/en-us/library/bb510624.aspx

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.