Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


adding one more column for SubTotal in Select statement


adding one more column for SubTotal in Select statement

Author
Message
kishorefeb28
kishorefeb28
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 96
HI EXPERTS,
need your help badly.......

We have a table with 2 columns 'OrderNo' and 'Amount' as below

ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000

as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column
so am writing a select statement with WHERE condition and
I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number
as below

ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500

please do help me achieve this

Thank you
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
Use GROUP BY. If you show us what you've tried so far, we can show you where you're going wrong.

John
kishorefeb28
kishorefeb28
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 96
thank you for the reply

Please check the attachment

here am unable to use Update statement as it shows error.

please check and help

thank you
Attachments
Temp_XACCD_SELECT.txt (26 views, 3.00 KB)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
I suspect that your error message tells you all you need to know, doesn't it? Just in case it doesn't, what does this return?

SELECT SUM(AMOUNT) FROM XACCD GROUP BY PORDER



John
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45315 Visits: 39936
This is really easy thanks to some (Windowing) functionality born in 2005.

I looked at your query (after I ran it through a reformatter) and the following will be similar. Note that I added an additional column to your test data to simulate the other columns in your query.

--===== Create and populate a test table on the fly.
-- This is NOT a part of the solution.
SELECT OrderNo, Something, Amount
INTO #TestTable
FROM (
SELECT '1D1ZX000','aaa',9262.5 UNION ALL
SELECT '1D1ZX001','ddd',9000.0 UNION ALL
SELECT '1D1ZX001','ddd',9000.0 UNION ALL
SELECT '1D1ZX002','ttt',10000 UNION ALL
SELECT '1D1ZX003','xxx',1000 UNION ALL
SELECT '1D1ZX003','bbb',200.50 UNION ALL
SELECT '1D1ZX003','zzz',100.50 UNION ALL
SELECT '1D1ZX004','qqq',500.0 UNION ALL
SELECT '1D1ZX004','rrr',1000 UNION ALL
SELECT '1D1ZX004','mmm',2000 UNION ALL
SELECT '1D1ZX004','sss',1000
)d(OrderNo,Something,Amount)
;




Then, you can actually get these types of subtotals without going through the ardure of a GROUP BY on so many columns. Like this...


SELECT OrderNo
,Something
,Amount
,SubTotal = SUM(Amount) OVER (PARTITION BY OrderNo)
FROM #TestTable
ORDER BY OrderNo, Something
;




For the test data and code above, here is the result set.
OrderNo  Something Amount   SubTotal
-------- --------- -------- ---------
1D1ZX000 aaa 9262.50 9262.50
1D1ZX001 ddd 9000.00 18000.00
1D1ZX001 ddd 9000.00 18000.00
1D1ZX002 ttt 10000.00 10000.00
1D1ZX003 bbb 200.50 1301.00
1D1ZX003 xxx 1000.00 1301.00
1D1ZX003 zzz 100.50 1301.00
1D1ZX004 mmm 2000.00 4500.00
1D1ZX004 qqq 500.00 4500.00
1D1ZX004 rrr 1000.00 4500.00
1D1ZX004 sss 1000.00 4500.00

(11 row(s) affected)



--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kishorefeb28
kishorefeb28
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 96
Thank you.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45315 Visits: 39936
kishorefeb28 (2/13/2014)
Thank you.


You bet. Thanks for the reply. Just to be sure, do you understand how it works?

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 6431
Not to be casting aspersions on Jeff's fine solution mind you, but I'd probably do it something like this:


WITH PreAggregate AS
(
SELECT OrderNo, SubTotal = SUM(Amount)
FROM #TestTable
GROUP BY OrderNo
)
SELECT a.OrderNo
,Something
,Amount
,SubTotal
FROM #TestTable a
JOIN PreAggregate b ON a.OrderNo = b.OrderNo
ORDER BY OrderNo, Something;




Reference: The Performance of the T-SQL Window Functions


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
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