A question on the ROLLUP operator

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

Transact-SQL (2000)

A question on the ROLLUP operator

Author

Topic

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-07-11 : 06:48:09

Hi all,

Suppose I have a simple table like this:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Table Yellow 167
Chair Blue 101
Chair Red 210
Chair Yellow 155

When we use the ROLLUP operator to do some totals and subtotals of the above data, is it possible to exclude some items in the calculation? for example, is it possible to exclude the Yellow Color when calculating the item total, but at the same time displaying the value of Yellow?

e.g., the following query will calculate the subtotal and total:

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

Result:

Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair Yellow 167.00
Chair ALL 478.00
Table Blue 124.00
Table Red 223.00
Table Yellow 155.00
Table ALL 502.00
ALL ALL 658.00

Is it possible to make the "ALL" total exclude the Yellow value, but at the same time continue to display the Yellow value in the result, within a single SELECT statement?

Thanks,
delpiero

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-11 : 06:52:32

Not as a single SELECT statement. "ALL" and "Total" mean just that; you can't exclude one value from that calculation unless you remove it from the entire query (by using a WHERE condition)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-11 : 09:58:38

Unless you add in another variable (eg 'name = fordisplayonly, values 0,1') which when grouped upon, will produce appropriate rollup totals which should give you what you want.

You would then have something like the following
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,
CASE WHEN (GROUPING(fordisplayonly) = 1) THEN 'ALL'
ELSE ISNULL(fordisplayonly, 'UNKNOWN')
END AS fordisplayonly,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color, fordisplayonly WITH ROLLUP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources