ROLLUP row has NULL descriptor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ROLLUP row has NULL descriptor

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-29 : 16:42:42

select Form02, Count(*) as Total from FormFeedback where Form02 <> '' Group By Form02 with rollup

Result
----------------
1-Strongly Disagree 2
2-Disagree 2
3-Neither 6
4-Agree 19
5-Strongly Agree 68
NULL 97

BOL states the ROLLUP row should show 'ALL', 97 instead of NULL, 97. Anyone know why NULL is showing in my result?

To go further with this, is there a way I can instruct ROLLUP rows to insert text I like such as 'Total Responses'?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-29 : 16:49:16

Just through an ISNULL() around the Form02 field:

SELECT ISNULL(Form02,'All') as Form02, COUNT(*) as Total
FROM FormFeedback WHERE form02 <> ''
GROUP BY Form02

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-29 : 16:51:52

'ALL' needs to come from your SQL Statement. Here's BOL's example:

This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00

(7 row(s) affected)

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end:

ALL Blue 225.00
ALL Red 433.00

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-29 : 16:54:34

Also, as an FYI, You Have Null Values in your table

Good Luck

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-29 : 16:59:00

Actually, yes, it is better to use the GROUPING() function than the ISNULL() in case you do normally have null values in the table. Probably performs better, too.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-29 : 17:26:38

Thank you everyone for your comments.

I pondered the Grouping function in the SELECT list. I hadn't seen it used there before.

Here's my feedback on all the previous posts.

I confirmed that there are no NULLs using

select form07 from FormFeecback where Form07 IS NULL

Then, going a step further, I extended the WHERE Clause to specify only NOT NULL elements. I still get ROLLUP returning a NULL title. I've tried it on some other tables without NULLs and I seem to consistently get the NULL in the row where ROLLUP should insert 'ALL'.

This is troublesome, since it sounds like an environmental problem, not a syntax problem. I think I'll direct my energies to the Grouping solution.

There's one problem I can forsee using Grouping in the select list - but it doesn't apply to the problem in this thread:

CASE Grouping in a select list only works when the variable type is character / text. It wouldn't work if it were say numeric, binary, bit.

Thanks again,

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-29 : 17:38:28

Your original statement will not return "ALL" because it does not contain this kind of expression:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item, ...

The CASE expression subsitutes ALL whenever the ROLLUP operator produces a grouping null. The behavior you're seeing is normal.

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-29 : 17:51:27

I'm now a member of the "have to be told twice club". I can strive for three times in another post.

I was positive that the 'ALL' was the default from a query I wrote months ago. I missed this point in BOL today. I discounted X0025's suggestion. Nuts!

Not wanting to admit I was wrong so easily, next I cracked open Henderson's book where I first read about ROLLUP. Someone obviously stole my book and changed the type to include CASE GROUPING

in the queries to produce the 'ALL' result.

Hey. Thanks for being patient

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-30 : 09:49:25

But did you get the results you are looking for?

Happy Hunting

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-30 : 10:01:06

I was so cross with missing the point I overlooked saying that the result works fine.

I'm also returning some HTML for emphasis:

CASE WHEN GROUPING(Field07) = 1 THEN '<b>Total Reciepants</b>' else Field07 END

to make the totals stand out.

Thanks,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources