CODEWITHSUNDEEP
sql-servert-sql
ExtremeSwat
ExtremeSwat

Reputation: 824

GROUPING_ID functionality

I don't quite understand how this function works, I've been looking over the below documentation, and I have some issues.

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

So, I understand how GROUPING() works perfectly, but the output for GROUPING_ID is quite impossible for me to comprehend how it's done because it's not the same with the explanation.

For example I have the following string of ones and zeroes: 010. In the documentation it says it's equal to 2. Also I read in a SQL book that each byte (the rightmost) is EQUAL with 2 at the power of the byte position minus one.

So, (2^2 - 1) + (2^1 - 1 ) + (2^0 - 1), but isn't that the same for each binary number? (100/101/110/etc), and the result isn't 2 either....

EDIT 1 : This is how the explanation from the book is:

Another function that you can use to identify the grouping sets is GROUPING_ID. This function accepts the list of grouped columns as inputs and returns an integer representing a bitmap. The rightmost bit represents the rightmost input. The bit is 0 when the respective element is part of the grouping set and 1 when it isn’t. Each bit represents 2 raised to the power of the bit position minus 1; so the rightmost bit represents 1, the one to the left of it 2, then 4, then 8, and so on. The result integer is the sum of the values representing elements that are not part of the grouping set because their bits are turned on. Here’s a query demonstrating the use of this function.

There has to be an error because there is no way a number is calculated by 2^(position) - 1, is it a mistake ? I've been calculating with 2^(bitposition) *1 and the outputs are correct. For example the I've done this

GROUPING_ID(a,b,c),
GROUPING(a),
GROUPING(b),
GROUPING(c)

And let's say we have the following output

3, 0, 1, 1

So our binary string is 011 and 3 is the output of the GROUPING_ID function, if we calculate the string

2^0 * 1 + 2^1 * 1 + 2^0 *2 = 1 + 2 + 0 = 3

There is no other logic that I see here, I cannot calculate with minus as the upper quote says, the thing is that on MSDN the weirder definition seems to be somewhat similar with this one:

Each GROUPING_ID argument must be an element of the GROUP BY list. GROUPING_ID () returns an integer bitmap whose lowest N bits may be lit.

A lit bit indicates the corresponding argument is not a grouping column for the given output row. The lowest-order bit corresponds to argument N, and the N-1th lowest-order bit corresponds to argument 1.

Upvotes: 2

Views: 2064

Answers (2)

Andriy M
Andriy M

Reputation: 77737

First of all, when they say

Each bit represents 2 raised to the power of the bit position minus 1

they do not mean 2position - 1 but rather 2position - 1. Apparently, for the purpose of their description they chose to count bits from 1 (for the rightmost bit) rather than from 0.

Secondly, each bit represents the said value when it is set, i.e. when it is 1. So, naturally, you do not do just

21 - 1 + 22 - 1 + ... + 2N - 1

but rather 

bit1 × 21 - 1 + bit2 × 22 - 1 + ... + bitN × 2N - 1

which is the normal way of converting the binary representation to the decimal one and is also the method you have shown near the end of your question.

Upvotes: 3

Dudi Konfino
Dudi Konfino

Reputation: 1136

Lets say we have binary number 0101 we went from right to left

1->(2^0*1)=1

0->(2^1*0)=0

1->(2^2*1)=4

0->(2^3*0)=0

if we summerize all results we have 5

so 0101(binary)=5(decimal)

Upvotes: 1

Related Questions