I am not able to get the meaning of choose function and grouping_id in the query.
This is Adventures database in sql server.
Specifically,
CHOOSE (1+ GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
a.City + ' Subtotal', a.StateProvince + ' Subtotal',
a.CountryRegion + ' Subtotal', 'Total'
My question is regarding the choose clause. As per the syntax , it is
CHOOSE ( index, val_1 val_2 [, val_n ] )
Furthermore, "grouping_id" should contain all list of columns which are in "GROUP BY".
If you could refer even to some article on grouping id.
I did my google search. What I understood seems insufficient.
Thanks for help
What I have tried:
SELECT a.CountryRegion, a.StateProvince, a.City,
CHOOSE (1+ GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
a.City + ' Subtotal', a.StateProvince + ' Subtotal',
a.CountryRegion + ' Subtotal', 'Total') AS Level,
SUM(soh.TotalDue) AS Revenue
FROM SalesLT.Address AS a
JOIN SalesLT.CustomerAddress AS ca
ON a.AddressID = ca.AddressID
JOIN SalesLT.Customer AS c
ON ca.CustomerID = c.CustomerID
JOIN SalesLT.SalesOrderHeader as soh
ON c.CustomerID = soh.CustomerID
GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
ORDER BY a.CountryRegion, a.StateProvince, a.City;