adding one more column for SubTotal in Select statement

Question

Post reply

adding one more column for SubTotal in Select statement

kishorefeb28

Ten Centuries

Points: 1087
More actions
February 13, 2014 at 5:43 am

#396264

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Use GROUP BY. If you show us what you've tried so far, we can show you where you're going wrong.

John

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 2

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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 SSC Guru Points: 1003859 More actions · Answer 4

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 '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

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 5

kishorefeb28

Ten Centuries

Points: 1087

February 13, 2014 at 9:40 pm

#1688967

Thank you.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

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 [/url]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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