Click here to Skip to main content
14,599,518 members
Rate this:
Please Sign up or sign in to vote.
See more:
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;
Posted
Updated 6-Jun-18 5:19am
v3
Comments
CHill60 5-Jun-18 11:57am
   
Member 13842073 5-Jun-18 13:03pm
   
Yes, I did read that. Still not clear
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
"grouping_id" should contain all list of columns which are in "GROUP BY"

Read the documentation again:
Each GROUPING_ID argument must be an element of the GROUP BY list.

You are grouping by a.CountryRegion, a.StateProvince, and a.City, so each of those column names is a valid argument for the GROUPING_ID aggregate function.

Quote:
My question is regarding the choose clause. As per the syntax , it is
CHOOSE ( index, val_1 val_2 [, val_n ] )

And that's exactly what you have:
CHOOSE (
    /* index = */  1 + GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City),
    /* val_1 = */  a.City + ' Subtotal',
    /* val_2 = */  a.StateProvince + ' Subtotal',
    /* val_3 = */  a.CountryRegion + ' Subtotal', 
    /* val_4 = */  'Total'
)


To understand the query properly, you'll also need to understand the ROLLUP clause:
SQL SERVER - Introduction to Rollup Clause - SQL Authority with Pinal Dave[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

No.
Do you have any idea how much work explaining code line by line is?
Every single line needs a paragraph of explanation! For example:
int next = r.Next();

Create a new variable called "next" which can hold a integer value. From the previously declared Random instance "r", call the "Next" method to get a new random number, and assign it to the "next" variable.

Can you imagine how long it would take us to explain even a very short code fragment like your example, line by line?

No. It is not going to happen. If you have a specific problem, then ask a question about it. But think first - would you want to sit down for 45 minutes and type up a line-by-line description for no good reason?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100