April 12, 2012 at 12:27 am
Dear All,
Hi! I need to generate a report to show Product_Category, Product_Code, Product_Name wise Stock & Sales details. To achieve the same I write simple Group by query & store this data in Temp. table.
But, if I need to add Sub-Total & Grand-Total on Product_Category basis with Product_Code, Product_Name in below given format then how can I achieve the same?
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
Group Query -
select Product_Group, Product_Code, Product_Name, Sum(Stock) as Stock, Sum(Sale) as Sale, Sum(Amt) as Amt into #tmp from Test
group by Product_Group, Product_Code, Product_Name order by Product_Group, Product_Code, Product_Name
select * from #tmp
Ouptut of this Query: -
Product_GroupProduct_CodeProduct_NameStockSaleAmt
StationaryA12Pencil6330300
StationaryA13Pen8361534
FurnitureF11Table11310568000
FurnitureF12Chair625555234
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
I need Output in below format: -
Product_GroupProduct_CodeProduct_NameStockSaleAmt
StationaryA12Pencil6330300
StationaryA13Pen8361534
Stationary Sub-Total14691834
FurnitureF11Table11310568000
FurnitureF12Chair625555234
Furniture Sub-Total175160123234
Grand-Total321251124068
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
But, when I am using ROLLUP then following output is coming: -
Query: -
select Product_Group, Product_Code, Product_Name, Sum(Stock) as Stock, Sum(Sale) as Sale, Sum(Amt) as Amt from #tmp
group by ROLLUP (Product_Group, Product_Code, Product_Name)
Output: -
Product_GroupProduct_CodeProduct_NameStockSaleAmt
FurnitureF11Table11310568000
FurnitureF11NULL11310568000
FurnitureF12Chair625555234
FurnitureF12NULL625555234
FurnitureNULLNULL175160123234
StationaryA12Pencil6330300
StationaryA12NULL6330300
StationaryA13Pen8361534
StationaryA13NULL8361534
StationaryNULLNULL14691834
NULLNULLNULL321251124068
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
Please guide what changes I have to make in Query to achieve the output in required format. Create & Insert quries are given below: -
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
CREATE TABLE [dbo].[#tmp](
[Product_Group] [varchar](1000) NULL,
[Product_Code] [varchar](1000) NULL,
[Product_Name] [varchar](1000) NULL,
[Stock] [int] NULL,
[Sale] [int] NULL,
[Amt] [numeric](18, 0) NULL
)
GO
Insert Into #tmp (Product_Group, Product_Code, Product_Name, Stock, Sale, Amt)
Values ('Stationary', 'A12', 'Pencil', 63, 30, 300),
('Stationary', 'A13', 'Pen', 83, 61, 534),
('Furniture', 'F11', 'Table', 113, 105, 68000),
('Furniture', 'F12', 'Chair', 62, 55, 55234)
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
April 12, 2012 at 2:15 am
The SQL-Jedi of this site will no doubt admonish you to let the presentation layer deal with this kind of sorting issue.
However since I am but a mere Paduan, I shall attempt to accommodate you.
CREATE TABLE [dbo].[#temp](
[Product_Group] [varchar](1000) NULL,
[Product_Code] [varchar](1000) NULL,
[Product_Name] [varchar](1000) NULL,
[Stock] [int] NULL,
[Sale] [int] NULL,
[Amt] [numeric](18, 0) NULL
)
Insert Into #temp (Product_Group, Product_Code, Product_Name, Stock, Sale, Amt)
SELECT 'Stationary', 'A12', 'Pencil', 63, 30, 300
UNION ALL SELECT 'Stationary', 'A13', 'Pen', 83, 61, 534
UNION ALL SELECT 'Furniture', 'F11', 'Table', 113, 105, 68000
UNION ALL SELECT 'Furniture', 'F12', 'Chair', 62, 55, 55234
;WITH r AS (
select '1' AS [Level], Product_Group, Product_Code, Product_Name, Sum(Stock) as Stock
, Sum(Sale) as Sale, Sum(Amt) as Amt
from #temp
group by Product_Group, Product_Code, Product_Name with ROLLUP
)
SELECT Product_Group, Product_Code, Product_Name, Stock, Sale, Amt
FROM (
SELECT CASE WHEN Product_Code IS NULL and Product_Group IS NULL THEN total ELSE Product_Group End AS Product_Group
, CASE WHEN Product_Code IS NULL and Product_Group IS NOT NULL THEN total ELSE Product_Code End AS Product_Code
, Product_Name, Stock, Sale, Amt
, CASE WHEN Product_Code IS NULL THEN x.level ELSE r.level END As Level
FROM r
LEFT OUTER JOIN (
SELECT 'Sub-Total', '1'
UNION ALL SELECT 'Grand-Total', '0') x (total, [level])
ON (Product_Code IS NULL and product_group IS NOT NULL and x.[level] = '1') OR
(Product_Code IS NULL and product_group IS NULL and x.[level] = '0')) y
order by [Level] DESC, Product_Group + [Level], Product_Code, Product_Name
select * from #temp
DROP TABLE #TeMP
Does this appear to be what you're looking for?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 12, 2012 at 5:06 am
Probably easier in SQL 2008 to use GROUPING SETS to accomplish the same result without 6 selects. This is built on the same data as Dwain.C without the CTE and subsequent selects. The Grouping Sets include the empty set () at the end for the grand total.
The CASE statements are included to tidy-up the output to remove the NULLs.
As Dwain.C mentioned this should be a presentation layer task but SQL can do it.
CREATE TABLE [dbo].[#temp](
[Product_Group] [varchar](1000) NULL,
[Product_Code] [varchar](1000) NULL,
[Product_Name] [varchar](1000) NULL,
[Stock] [int] NULL,
[Sale] [int] NULL,
[Amt] [numeric](18, 0) NULL
)
Insert Into #temp (Product_Group, Product_Code, Product_Name, Stock, Sale, Amt)
SELECT 'Stationary', 'A12', 'Pencil', 63, 30, 300
UNION ALL SELECT 'Stationary', 'A13', 'Pen', 83, 61, 534
UNION ALL SELECT 'Furniture', 'F11', 'Table', 113, 105, 68000
UNION ALL SELECT 'Furniture', 'F12', 'Chair', 62, 55, 55234
SELECT
CASE WHEN Product_Group IS NULL THEN 'Grand Total' ELSE Product_Group END AS Product_Group,
CASE WHEN Product_Group IS NOT NULL and Product_Code IS NULL THEN 'Sub Total'
ELSE isnull(Product_Code,'') END AS Product_Code
, isnull(Product_Name,'') AS ProductName,
sum(Stock) AS TotalStock, sum(Sale) AS TotalSale,SUM(Amt) AS TotalAmt
FROM #temp
GROUP BY GROUPING SETS
((Product_Group, Product_Code, Product_Name), -- individual product rows
(Product_Group), -- group totals
()) -- grand totals
DROP TABLE #TeMP
Fitz
April 12, 2012 at 5:13 am
Dear SSC Veteran,
Thanks for the reply! But, have you checked the output of query? It is same as the output given by ROLLUP.
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
I need Output in below format: -
Product_Group Product_Code Product_Name Stock Sale Amt
Stationary A12 Pencil 63 30 300
Stationary A13 Pen 83 61 534
Stationary Sub-Total 146 91 834
Furniture F11 Table 113 105 68000
Furniture F12 Chair 62 55 55234
Furniture Sub-Total 175 160 123234
Grand-Total 321 251 124068
--<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>-- --<><>--
& your query is giving the output : -
Product_GroupProduct_CodeProduct_NameStockSaleAmt
FurnitureF11NULL11310568000
FurnitureF11Table11310568000
FurnitureF12NULL625555234
FurnitureF12Chair625555234
FurnitureSub-TotalNULL175160123234
StationaryA12NULL6330300
StationaryA12Pencil6330300
StationaryA13NULL8361534
StationaryA13Pen8361534
StationarySub-TotalNULL14691834
Grand-TotalNULLNULL321251124068
Please guide how to achieve the output in required format. If possible then by ROLLUP else the way which is not effecting performance.
April 12, 2012 at 5:16 am
Dear Fitz,
Thanks for the reply! the query is giving the output as per the requirement. But, what if we are using SQL 2005??? Is there any way by which we can do the same in SQL 2005 version.
April 12, 2012 at 5:18 am
SQL 2005 see Dwain.C answer above.
April 12, 2012 at 5:21 am
Sorry! I am asking for SQL 2000 version. In SQL 2000 we can achieve the same by UNION or by using multiple temp tables or any other way which reduce the sql coding & improve the performance.
April 12, 2012 at 5:31 am
Mark,
I didn't particularly like my solution but thought that it worked.
Looked like the OPs expected result set, even sorted right through mutliple machinations.
Didn't have access to SQL 2008 when I posted this, but then apparently neither does the OP. May give your solution a whirl now that I'm home to see if I can grok it.
Love learning new stuff!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply