GROUPING SETS, GROUPING_ID(), CUBE, ROLLUP

GROUP BY 
a new operator - the GROUPING SETS operator
a new function - the GROUPING_ID() function

select
t.[Group], t.CountryRegionCode, SUM(h.TotalDue)
from
sales.SalesTerritory t
inner join sales.SalesOrderHeader h on t.TerritoryID= h.TerritoryID
GROUP BY
t.[Group], t.CountryRegionCode
UNION ALL
select t.[Group], NULL, SUM(h.TotalDue) from ... GROUP BY t.[Group]
UNION ALL
select NULL, NULL, SUM(h.TotalDue) from  ...


Group CountryRegionCode (No column name)
Pacific AU 11814376.0952
North America CA 18398929.188
Europe DE 5479819.5755
Europe FR 8119749.346
Europe GB 8574048.7082
North America US 70829863.203
Europe NULL 22173617.6297
North America NULL 89228792.391
Pacific NULL 11814376.0952
NULL NULL 123216786.1159

select
t.[Group], t.CountryRegionCode, SUM(h.TotalDue),GROUPING_ID(t.[Group]),
GROUPING_ID(t.CountryRegionCode), GROUPING_ID(t.[Group], t.CountryRegionCode)
from
sales.SalesTerritory t
inner join sales.SalesOrderHeader h on t.TerritoryID= h.TerritoryID
GROUP BY GROUPING SETS
((t.[Group], t.CountryRegionCode),(t.[Group]),())

Group CountryRegionCode Total GID GID GID
Europe DE 5479819.5755 0 0 0
Europe FR 8119749.346 0 0 0
Europe GB 8574048.7082 0 0 0
Europe NULL 22173617.6297 0 1 1
North America CA 18398929.188 0 0 0
North America US 70829863.203 0 0 0
North America NULL 89228792.391 0 1 1
Pacific AU 11814376.0952 0 0 0
Pacific NULL 11814376.0952 0 1 1
NULL NULL 123216786.1159 1 1 3


Group CountryRegionCode
AU CA DE FR GB US Total
Pacific GROUP BY(Group, CountryRegionCode) GROUP BY([Group])
North America
Europe
ALL GROUP BY(CountryRegionCode) GROUP BY()

GROUP BY GROUPING SETS((t.[Group], t.CountryRegionCode),(t.CountryRegionCode),(t.[Group]),())
Equivalent to 
CUBE => GROUP BY CUBE (t.[Group], t.CountryRegionCode) OR
GROUP BY t.[Group], t.CountryRegionCode WITH CUBE
Generates the GROUP BY aggregate, sub total of group by aggregate, aggregate of group by and total row.

Group CountryRegionCode (No column name)
Pacific AU 11814376.0952
NULL AU 11814376.0952
North America CA 18398929.188
NULL CA 18398929.188
Europe DE 5479819.5755
NULL DE 5479819.5755
Europe FR 8119749.346
NULL FR 8119749.346
Europe GB 8574048.7082
NULL GB 8574048.7082
North America US 70829863.203
NULL US 70829863.203
NULL NULL 123216786.1159
Europe NULL 22173617.6297
North America NULL 89228792.391
Pacific NULL 11814376.0952

ROLLUP => GROUP BY ROLLUP (t.[Group], t.CountryRegionCode) OR GROUP BY t.[Group], t.CountryRegionCode WITH ROLLUP 
Generates the GROUP BY aggregate, aggregate of group by and total row.

Group CountryRegionCode (No column name)
Europe DE 5479819.5755
Europe FR 8119749.346
Europe GB 8574048.7082
Europe NULL 22173617.6297
North America CA 18398929.188
North America US 70829863.203
North America NULL 89228792.391
Pacific AU 11814376.0952
Pacific NULL 11814376.0952
NULL NULL 123216786.1159