SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subtotal when vendorcode changed.


Subtotal when vendorcode changed.

Author
Message
kishorefeb28
kishorefeb28
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 96
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.
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2360 Visits: 979
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.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42768 Visits: 19847
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219416 Visits: 42002
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jdr.suporte
jdr.suporte
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 40
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;


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219416 Visits: 42002
jdr.suporte (1/31/2014)
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;



There's just no need to hit the table twice. Seriously, lookup WITH ROLLUP.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kishorefeb28
kishorefeb28
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 96
Thank you Luis

i found its not recommended so i have requested to handle it in the front end .
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219416 Visits: 42002
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42768 Visits: 19847
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
kishorefeb28
kishorefeb28
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 96
THANK YOU.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search