group by with rollup rounding issue

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 2005 Forums

Transact-SQL (2005)

group by with rollup rounding issue

Author

Topic

OrientExpress01
Starting Member

1 Post

Posted - 2010-07-01 : 14:18:36

Need help with rounding only the subtotals in a rollup.
Below is a simplified example of what I am doing.
After running the select script, I would like the Total_Costs for only the Report Total to be rounded.
Is this possible with a Case statement?

--Table Creation

CREATE TABLE [dbo].[SalesItems](
[Product] [nchar](10) NULL,
[Store] [nchar](10) NULL,
[Cost] [numeric](18, 5) NULL
)
GO
INSERT INTO SalesItems VALUES ('Chips','A',2.50000)
INSERT INTO SalesItems VALUES ('Chips','B',3.00000)
INSERT INTO SalesItems VALUES ('Napkins','A',1.90000)
INSERT INTO SalesItems VALUES ('Chips','B',3.00000)
INSERT INTO SalesItems VALUES ('Napkins','B',2.50000)
INSERT INTO SalesItems VALUES ('Dip','A',4.00000)
INSERT INTO SalesItems VALUES ('Dip','A',4.30000)
INSERT INTO SalesItems VALUES ('Dip','B',3.40000)
INSERT INTO SalesItems VALUES ('Chips','A',2.75000)
INSERT INTO SalesItems VALUES ('Napkins','A',2.10000)
INSERT INTO SalesItems VALUES ('Napkins','B',2.60000)
GO

--Select Script

SELECT CASE WHEN (Grouping(Store) = 1) THEN 'Report Total' ELSE Store END AS report_total, Product, Store, SUM(Cost) AS

Total_Cost
FROM SalesItems
GROUP BY Product, Store WITH ROLLUP
ORDER BY report_total

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 23:16:18

You can use a derived table for it.Something like this.


select report_total,Product,Store,
case when Store is null then ROUND(Total_Cost,1) else Total_Cost end
 from
(
SELECT 
CASE WHEN (Grouping(Store) = 1) THEN 'Report Total' ELSE Store END AS report_total, 
Product, Store, SUM(Cost) AS

Total_Cost
FROM SalesItems
GROUP BY Product, Store WITH ROLLUP

)t

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources