Grouping With Rollup Question - SQL Server Forums

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)

Grouping With Rollup Question

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-03-07 : 10:36:08

Maybe it's something simple, maybe it can't be done, either way, here is the problem. I have the following select statement:


    SELECT TOP 100 PERCENT
           YEAR([cb].[CreatedOn]) AS [Capital Budget Year],
           [d].[DepartmentID] AS [DepartmentID],
           [d].[Name] AS [Department],
           [cb].[Year] AS [In Service Year],
           SUM([cbi].[Value]) AS [Total]
      FROM [dbo].[CapitalBudgets] AS [cb]
           INNER JOIN [dbo].[CapitalBudgetItems] AS [cbi] ON [cb].[CapitalBudgetID] = [cbi].[CapitalBudgetID]
           INNER JOIN [dbo].[Departments] AS [d] ON [cbi].[DepartmentID] = [d].[DepartmentID]
     WHERE [cbi].[CapitalBudgetItemTypeID] = [dbo].[CapitalBudgetItemTypes$GetID]('Hardware Cost')
     GROUP BY YEAR([cb].[CreatedOn]),
              [d].[DepartmentID],
              [d].[Name],
              [cb].[Year]
              WITH ROLLUP
    HAVING GROUPING([d].[DepartmentID]) = 0
       AND GROUPING([d].[Name]) = 0
       AND GROUPING([cb].[Year]) = 0
     ORDER BY [d].[Name],
              [cb].[Year]

That gives the following results: (Created Year, Department ID, Department, In Service Year, Total)


2002	170	All Stores General	2002	13392719.0000
2002	97	Consumer Affairs	2002	18729.0000
2002	117	General Maintenance	2002	2549.0000
2002	122	Information Technology	2002	991894.0000
2002	123	Internet Marketing	2002	8500.0000
2002	140	Store Maintenance	2002	6427.0000
--- abridged ---

This information is correct, however, I am trying to manipulate the select statement so it will give me the total for this set at the bottom. I used the HAVING CLAUSE to get rid of the items that are not important, such as totals for departments, I only want a total total for all items in the result set, so something like:


NULL    NULL    NULL                    NULL    31202122.0000

Would be at the bottom of the result set. I've used this before but I'm not sure if the HAVING clause is having an adverse effect. Any help will be much appreciated...

Thanks,
... Onamuji

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-03-07 : 11:03:34

Let's make this a little more complex, I want to summarize some data into a single select statement. Currently it is 3 unions... I'm trying something similar to the first statement, except I just need summarized data for each type, so a total for type 1, 2, and 3. I don't totals for any other column (department, year).


    SELECT TOP 100 PERCENT
           YEAR([cb].[CreatedOn]) AS [Capital Budget Year],
           [d].[DepartmentID] AS [DepartmentID],
           [d].[Name] AS [Department],
           [cb].[Year] AS [In Service Year],
           SUM(CASE [types].[Value]
                   WHEN 1 THEN CASE [cbi].[CapitalBudgetItemTypeID] WHEN [dbo].[CapitalBudgetItemTypes$GetID]('Hardware Cost') THEN [cbi].[Value] ELSE 0 END
                   WHEN 2 THEN CASE [cbi].[CapitalBudgetItemTypeID] WHEN [dbo].[CapitalBudgetItemTypes$GetID]('Software Cost') THEN [cbi].[Value] ELSE 0 END
                   WHEN 3 THEN [cbi].[Value]
               END) AS [Total],
           [years].[MinYear],
           [years].[MaxYear],
           [types].[Value] AS [Type]
      FROM [dbo].[CapitalBudgets] AS [cb]
           INNER JOIN [dbo].[CapitalBudgetItems] AS [cbi] ON [cb].[CapitalBudgetID] = [cbi].[CapitalBudgetID]
           INNER JOIN [dbo].[Departments] AS [d] ON [cbi].[DepartmentID] = [d].[DepartmentID]
           INNER JOIN (SELECT YEAR([CreatedOn]) AS [Capital Budget Year],
                              MIN([Year]) AS [MinYear],
                              MAX([Year]) AS [MaxYear]
                         FROM [dbo].[CapitalBudgets]
                        GROUP BY YEAR([CreatedOn])) AS [years] ON YEAR([cb].[CreatedOn]) = [years].[Capital Budget Year]
           CROSS JOIN (SELECT 1 AS [Value] UNION SELECT 2 UNION SELECT 3) AS [types]
     WHERE [cbi].[CapitalBudgetItemTypeID] IN ([dbo].[CapitalBudgetItemTypes$GetID]('Hardware Cost'), [dbo].[CapitalBudgetItemTypes$GetID]('Software Cost'))
     GROUP BY YEAR([cb].[CreatedOn]),
              [d].[DepartmentID],
              [d].[Name],
              [cb].[Year],
              [years].[MinYear],
              [years].[MaxYear],
              [types].[Value]
              WITH ROLLUP
     ORDER BY GROUPING([types].[Value]),
              [types].[Value],
              GROUPING([d].[Name]),
              [d].[Name],
              GROUPING([cb].[Year]),
              [cb].[Year]

So what do you think, am I totally helpless. :-\ The data I get back is not what I want, well without the rollup it is but I want the rollup to work for me so I don't have to total the types in presentation code...

Let see just how good you guys are ;-)

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-09 : 16:38:18

One work around is use UNION and drop the ROLLUP.

select a,b,c...
from..
group by a,b,c
UNION
select a='Total',b,c...
from..
group by b,c

order by
case when a='total' then 'zzz9999' else cast(a as varchar) end

Union places the "bottom" statement as the top row so you need the order by; or you could switch the order of the statements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources