Setting up running subtotals in a query

OrdNo Qty
1         100
1         150
2         100
3         100
3         200
3          50

What I am looking for is a query that returns

OrdNo Qty  OrderQty
1         100    0
1         150   250
2         100   100
3         100    0
3         200    0
3          50     350

Select a.OrdNo, a.Qty,
OrderQty = (Select SUM(Qty) From Table1 WHERE OrdNo = a.OrdNo)
FROM Table1  a

produces the following:

OrdNo Qty  OrderQty
1         100    250
1         150    250
2         100   100
3         100    350
3         200    350
3          50     350

But how to put zero instead of subotal to all but the last record in a group? (so that the sum of that column would return the total quantity)

Thank you!
larisa1970Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
;WITH CTE As
 (SELECT ordNo, qty, Rn  = ROW_NUMBER() OVER( PARTITION BY OrdNo ORDER BY OrderTime DESC)
 FROM Table1  )
 
 SELECT ordNo, qty, case when Rn = 1 THEN (SELECT SUM (Qty) FROM CTE WHERE ordNo = c.ordNo ) else 0 end
 from CTE c
 order by c.ordNo asc, Rn desc
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
which database / version ? also is there any unique  key in the table
0
 
larisa1970Author Commented:
SQL 2008.
There's no primary key, but there is a Order Time, that's pretty much unique, so the latest Order should get the subtotal number and the earlier ones from that group should get 0.

thank you
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
technofileCommented:
Select a.OrdNo,
OrderQty = (Select SUM(Qty) From Table1 WHERE OrdNo = a.OrdNo)
FROM Table1  a
Group By a.OrdNo would give you.
OrdNo OrderQty
1          250
2          100
3          350

But itemizing and getting a zero is not possible. Also what your asking for doesn't make a whole lot of sense if you are totalling the Qty in another program just sum the a.Qty. If you explain what you are trying to do a little better maybe I can be more help.
0
 
larisa1970Author Commented:
Here is why I need it -  The data is stock trading executions - some times an order is executed once, some times it is executed in parts (partial executions). So each row is an "execution" while each Order can contain one or many executions.
I need to do further calculations - some on "per-order" some on "per-execution" basis.
Was hoping to do it in one query, instead of multiple queries or temp tables
0
 
larisa1970Author Commented:
Thank you, aneeshattingal.
That's exactly what I need
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.