tags:

views:

171

answers:

1

environment: SQL server 2008 enterprise edition.

I am using Rollup clause and you how it shows aggregation at various levels WITH NULL values showing different levels of rollups e.g. ROllup(year,month,week) would show subtotals at each level.

I want it rolled up and yet want to see only highest elvel of aggregation. so I dont want to see any null values.

ANy idea how can i do that?

Regards Manjot

+3  A: 

What do you mean by "only highest level of aggragation?

You can avoid the NULLs by checking if a column in grouped, like so:

SELECT        CASE WHEN Grouping(GroupID) = 1 THEN '#ALL' ELSE GroupID END AS         GroupID,          
              CASE WHEN Grouping(SubGroupID) = 1 THEN '#ALL' ELSE SubGroupID END AS SubGroupID, 
              Sum(Value)
FROM          Table
GROUP BY      GroupID,
              SubGroupID
WITH ROLLUP

It will display #ALL insteaed of NULL.

Maximilian Mayerl
thank you very much
Manjot
@#$% YA! up vote on that one! nice solution! I was using ISNULL but then I was running into problems with when the actual value should have been null! THANKS!
kralco626