Trusting The Material

My primary resource in studying for the 70-433 exam is Microsoft’s Self-Paced Training Kit for MCTS Exam 70-433. While I understand that mistakes will happen, I was quite disappointed to find that their description of GROUP BY in relation to ROLLUP and CUBE was actually wrong!

The topic of ROLLUP and CUBE in addition to GROUP BY can get a bit confusing, I’ll admit, but it was the GROUP BY portion that they got wrong in the book!

The book gives the following query (for AdventureWorks2008)  as an example:

SELECT Production.Product.ProductSubcategoryID
, AVG(Listprice) AS 'Average'
, MIN(Listprice) AS 'Minimum'
, MAX(Listprice) AS 'Maximum'
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY Product.ProductSubcategoryID

The book then cites the explanation of the query results as:

“The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum, and maximum list prices of products across all subcategories.”

NO. No it isn’t. It’s the average, minimum and maximum of all entries in the Production.Product table that have a ProductSubcategoryID of NULL. And here’s how to prove it.

If you query the table, you’ll see there are 209 entries with a ProductSubcategoryID of NULL. If we perform these aggregate functions on just those NULL ProductSubcategoryIDs, we get the exact same results:

SELECT Production.Product.ProductSubcategoryID
, AVG(Listprice) AS 'Average'
, MIN(Listprice) AS 'Minimum'
, MAX(Listprice) AS 'Maximum'
FROM Production.Product
WHERE ListPrice <> 0
AND ProductSubcategoryID IS NULL
GROUP BY Product.ProductSubcategoryID

Total rows that appear with the label of NULL are a product of using WITH ROLLUP or WITH CUBE with a GROUP BY but any NULLs you see in a plain old GROUP BY are a product of the actual table data.

While I’m happy that I understand this topic and I’m not worried about it on the exam (unless the test creator is also the book author/editor, uh-oh!), I am a bit concerned about the quality of material I’m learning from. What happens if the information they provide about querying XML or Full Text are equally as “off”? I could be going into this exam with the completely wrong information and that would be a massive waste.