June 30, 2010 at 2:09 pm
Hi all,
I currently have a script which summarizes data using the group by with rollup command. I was wondering if there is an easy way to round the subtotal.
example:
SELECT Product, Store, SUM(Cost) as Cost
FROM SalesItems
GROUP BY Product,Store
WITH ROLLUP
I want Cost to remain in decimals at the product/store level, but the subtotals and total to be rounded.
Thanks!
June 30, 2010 at 2:18 pm
One option would be to wrap your example inside a cte and use a conditional conversion in an outer query (using CASE statement).
If you'd like to see a coded version of what I'm talking about, please provide table def and sample data in a ready to use format as described in the first link referenced in my signature.
June 30, 2010 at 5:26 pm
Ok, I am still pretty new to SQL.
So is the CASE statement you're talking about something like this (building on the example earlier)
SELECT
CASE WHEN (Grouping(Store)=1) THEN 'Report Total' ELSE Store END AS report_total,
CASE WHEN (Grouping(Product)=1)Then 'Product Subtotal' ELSE Product END AS grouping_total,
Product,
Store,
SUM(Cost) as Cost
FROM SalesItems
GROUP BY Product,Store
WITH ROLLUP
Order by report_total, grouping_total
July 1, 2010 at 11:17 am
Ok, so here's a simplified version of what I want to do. After running the Report Script, I want only the 'Total_Costs' for all 'Report Total' to be rounded.
I've tried a few variations of Case statements, but was unable to get it to work.
--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
--Report 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
July 1, 2010 at 4:46 pm
Would the following code give you the expected result?
SELECT
CASE WHEN (Grouping(Store) = 1) THEN 'Report Total' ELSE Store END AS report_total,
Product,
Store,
CASE WHEN (Grouping(Store) = 1) THEN round(SUM(Cost),0) ELSE SUM(Cost) END AS Total_Cost
FROM SalesItems
GROUP BY Product, Store WITH ROLLUP
ORDER BY report_total
July 6, 2010 at 12:09 pm
thats exactly what I needed, thanks!
July 6, 2010 at 12:16 pm
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply