Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

adding one more column for SubTotal in Select statement Expand / Collapse
Author
Message
Posted Thursday, February 13, 2014 5:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, 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
Post #1541134
Posted Thursday, February 13, 2014 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 5,077, Visits: 8,916
Use GROUP BY. If you show us what you've tried so far, we can show you where you're going wrong.

John
Post #1541137
Posted Thursday, February 13, 2014 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, 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



  Post Attachments 
Temp_XACCD_SELECT.txt (23 views, 3.02 KB)
Post #1541142
Posted Thursday, February 13, 2014 6:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 5,077, Visits: 8,916
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
Post #1541145
Posted Thursday, February 13, 2014 6:41 AM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541158
Posted Thursday, February 13, 2014 9:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
Thank you.
Post #1541467
Posted Friday, February 14, 2014 8:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541631
Posted Sunday, February 23, 2014 6:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 3,590, Visits: 5,097
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!
Post #1544337
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse