January 31, 2014 at 7:10 am
Hi Experts,
i need to write a stored procedure for a table with below columns
for instance the table had 5 columns like
VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500
so am writing a Stored Proc to get all the table values like above ., but i need to write Stored Proc in such a way it returns
the above table like result along with subtotal when ever vendor code changed. as below
VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
subtoal 110 30 1200 ----- this subtoal should be displayed from Stored procedure
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500
subtotal 25 105 600
please help me to acheive this
thank you.
January 31, 2014 at 9:02 am
Please read the article in my signature on posting questions like this on the forum. Please provide the DDL statements along with the sample data inserts.
January 31, 2014 at 9:20 am
Do you really need to do this in T-SQL? This is usually made in the front end.
If it's completely needed on T-SQL, you might need to union the detail with a query with subtotals adding a (non-visible) column to order the results so the subtotal will come after (or before if needed) the detail.
For a coded response, follow Keith's advice.
January 31, 2014 at 3:02 pm
kishorefeb28 (1/31/2014)
Hi Experts,i need to write a stored procedure for a table with below columns
for instance the table had 5 columns like
VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500
so am writing a Stored Proc to get all the table values like above ., but i need to write Stored Proc in such a way it returns
the above table like result along with subtotal when ever vendor code changed. as below
VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
subtoal 110 30 1200 ----- this subtoal should be displayed from Stored procedure
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500
subtotal 25 105 600
please help me to acheive this
thank you.
Use GROUP BY WITH ROLLUP. See Books Online. As the others have suggested, if you want a coded answer, please see the first link in my signature line below under "Helpful Articles".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2014 at 4:00 pm
Try
;with
T as (
SELECT VendorCode, 1 as Seq, UnitPrice, Qty, (UnitPrice * Qty) as Total
from Sales
union
SELECT VendorCode, 2, sum(UnitPrice), sum(Qty), sum(UnitPrice * Qty)
from Sales
group by VendorCode
)
SELECT case when Seq=1 then VendorCode else 'subtotal' end as VendorCode,
UnitPrice, Qty, Total
from T
order by T.VendorCode, Seq;
January 31, 2014 at 9:10 pm
jdr.suporte (1/31/2014)
Try
;withT as (
SELECT VendorCode, 1 as Seq, UnitPrice, Qty, (UnitPrice * Qty) as Total
from Sales
union
SELECT VendorCode, 2, sum(UnitPrice), sum(Qty), sum(UnitPrice * Qty)
from Sales
group by VendorCode
)
SELECT case when Seq=1 then VendorCode else 'subtotal' end as VendorCode,
UnitPrice, Qty, Total
from T
order by T.VendorCode, Seq;
There's just no need to hit the table twice. Seriously, lookup WITH ROLLUP.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2014 at 11:51 pm
Thank you Luis
i found its not recommended so i have requested to handle it in the front end .
February 5, 2014 at 7:49 pm
kishorefeb28 (2/4/2014)
i found its not recommended so i have requested to handle it in the front end .
You obviously didn't hear that from me. 😉 There's absolutely no reason why this can't be done in T-SQL and it's one less piece of managed code that you would have to resource, recompile, and repromote if you needed to make a change. Think of it as easy encapsulation. 😉
First, you took more time trying to format your request than doing it the right way. If you had done it the right way, it wouldn't have deterred someone like me from actually giving it try for almost a week. The "Right Way" is documented in the in the first link in my signature line below under "Useful Links".
I'll do it for you this time only because you've been misled by someone into thinking that doing it in T-SQL is "Not Recommended". Yeah, there's a whole lot of people that will say that. I just don't happen to agree with them for the reasons previously mentioned.
Here's the test table setup if anyone else wants to play with some of the more advanced features of GROUP BY that came out in 2008. I kept this one at a 2005 (or up) level because it's a common question across all versions.
SELECT *
INTO #TestTable
FROM (
SELECT 'V001',100, 10,1000 UNION ALL
SELECT 'V001', 10, 20, 200 UNION ALL
SELECT 'V222', 20, 5, 100 UNION ALL
SELECT 'V222', 5,100, 500
)d(VendorCode,UnitPrice,Qty,Total)
;
See? Not much more difficult than the formatting you tried to do.
Also, you wrote that you wanted sub-totals of the Unit Prices and the Qty's. Unless the parts (or whatever) are all identical, it makes no sense to sub-total or total those columns. With that in mind, I've written a CASE statement into the code for those two columns to output just a blank for sub-totals. That's easy to change, of course.
On to the code. The following produces the desired output (without the subtotals for 2 columns as per above). I personally don't believe you need the ORDER BY for this one but a lot of people get all bent out of shape if it's not there. Since it doesn't hurt anything by being there, I've included it.
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER(ORDER BY VendorCode)
,VendorCode,UnitPrice,Qty,Total
FROM #TestTable
)
SELECT VendorCode = CASE
WHEN GROUPING(VendorCode) = 0 AND GROUPING(RowNum) = 0 THEN VendorCode
WHEN GROUPING(VendorCode) = 0 AND GROUPING(RowNum) = 1 THEN 'Sub Total'
WHEN GROUPING(VendorCode) = 1 AND GROUPING(RowNum) = 1 THEN 'Grand Total'
END
,UnitPrice = CASE WHEN GROUPING(RowNum) = 0 THEN CAST(SUM(UnitPrice) AS VARCHAR(10)) ELSE '' END
,Qty = CASE WHEN GROUPING(RowNum) = 0 THEN CAST(SUM(Qty) AS VARCHAR(10)) ELSE '' END
,Total = SUM(Total)
--,MAX(RowNum),GROUPING(VendorCode),GROUPING(RowNum) --Uncomment this line to see how it all works
FROM cteEnumerate
GROUP BY VendorCode,RowNum WITH ROLLUP
ORDER BY MAX(RowNum),GROUPING(VendorCode),GROUPING(RowNum)
;
Here's the output from the above...
VendorCode UnitPrice Qty Total
----------- ---------- ---------- -----------
V001 100 10 1000
V001 10 20 200
Sub Total 1200
V222 20 5 100
V222 5 100 500
Sub Total 600
Grand Total 1800
(7 row(s) affected)
I'd write a full blown explanation of the code for you, but you have to have some of the fun. 😉 I strongly recommend you lookup the things I used in Books Online so that you're not not so easily lured into believing in someone's recommendation that "it's not recommended" .
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2014 at 10:49 am
Jeff Moden (2/5/2014)
kishorefeb28 (2/4/2014)
i found its not recommended so i have requested to handle it in the front end .You obviously didn't hear that from me. 😉 There's absolutely no reason why this can't be done in T-SQL and it's one less piece of managed code that you would have to resource, recompile, and repromote if you needed to make a change. Think of it as easy encapsulation. 😉
There's a simple reason of why would I do this on the front end. Formatting totals and subtotals is a lot simpler that way. That's, of course, depending on what the front end is.
As you say: "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." But it doesn't mean you should, what you need to do is to evaluate different options and now is time for the OP to decide.
February 13, 2014 at 5:24 am
THANK YOU.
February 13, 2014 at 7:04 am
Luis Cazares (2/6/2014)
There's a simple reason of why would I do this on the front end. Formatting totals and subtotals is a lot simpler that way.
If the front-end code has capabilities similar to the Matrix capabilites of SSRS, I'd agree. But even then, it's good to know how to do these types of things in T-SQL because a front end doesn't always have the capability to do it easily and, sometimes, there is no front end to be had.
It's also a heck of a lot easier to deploy and maintain than managed code.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2014 at 8:31 am
Jeff Moden (2/13/2014)
Luis Cazares (2/6/2014)
There's a simple reason of why would I do this on the front end. Formatting totals and subtotals is a lot simpler that way.If the front-end code has capabilities similar to the Matrix capabilites of SSRS, I'd agree. But even then, it's good to know how to do these types of things in T-SQL because a front end doesn't always have the capability to do it easily and, sometimes, there is no front end to be had.
It's also a heck of a lot easier to deploy and maintain than managed code.
Totally agree. The best tool to do something is defined by the situation and it's good to know all the alternatives.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply