January 1, 2011 at 7:07 pm
Sorry in advance for ignorance. I tried searching around a bit for intelligible info and didn't come up with much, hence my post.
I'm reading Itzik Ben-Gan's book "T-SQL Fundamentals" right now, and Chapter 7 is the first one so far that's feeling really fuzzy to me. It has to do with CUBE, ROLLUP, GROUPING, and GROUPING_ID.
Most of this stuff sorta makes sense to me, theoretically, but I'm having a hard time correlating it to real-world situations.
I don't really even know where to start with my questions, and I don't understand things well enough to word this intelligently...but how about this:
1. What is the point of using GROUPING? I know it displays a "0" if there is valid data in the related column, and "1" if NULL. Is GROUPING simply a convenience that allows you to write something like "HAVING GROUPING(column)>0" instead of using a CTE/derived table and writing "WHERE column IS NULL"?
2. Similar question for GROUPING_ID. Is it simply more convenient than checking for IS NULL and IS NOT NULL on the relevant columns? It seems useless, why wouldn't you just check the columns themselves for NULL/NOT NULL directly instead of using a confusingly-calculated integer?
3. The author (sort of) explains that the GROUPING_ID number is an integer that is derived from a "binary". Further research has slightly illuminated that he's referring to a "bit vector" or "bit array"...?
How are these numbers calculated, why are they useful, and why is SQL doing this to me?
January 2, 2011 at 11:25 am
1. Grouping: for example, when you use ROLLUP, you have totals and subtotals in your result set. GROUPING just indicates if you're dealing with (sub)totals or with data of the lowest detail. So it is not a check to see if it is NULL.
Example (from BOL):
USE AdventureWorks;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO
SalesQuota TotalSalesYTD Grouping
--------- ------------- --------
NULL 1533087.5999 0
250000.00 33461260.59 0
300000.00 9299677.9445 0
NULL 44294026.1344 1
The first NULL is the result for all rows where SalesQuota is NULL. The 2nd and 3rd rows are results of lowest detail. The final NULL is the grand total that sums up the first 3 rows.
2. Grouping_ID: I don't really understand it myself 🙂 But it is not a function to check for NULL, it is more a complex GROUPING.
Now, don't let this put you down, this is pretty advanced stuff that you won't need most of the time 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 3, 2011 at 11:39 am
Okay, thanks for the answer. I think I was basically on the right track, that the whole "Grouping" thing is just a convenient way to check for NULL intersections. And if you understand "Grouping", I don't think you'll have problems with GROUPING_ID, as it's fairly similar.
Also good to know this is somewhat esoteric stuff that I won't probably have to deal with too often in real life. 🙂
January 4, 2011 at 12:10 am
autoexcrement (1/3/2011)
Okay, thanks for the answer. I think I was basically on the right track, that the whole "Grouping" thing is just a convenient way to check for NULL intersections.
What's important about GROUPING is that they're not just ordinary NULLs. If GROUPING is 0, it means that those are the aggregated results of all the NULL values in your table. If it is 1, those NULLS symbolize a sub- or grandtotal.
Anyway, good luck with the learning!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply