[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Setting up running subtotals in a query

Posted on 2009-12-30
6
Medium Priority
?
182 Views
Last Modified: 2012-05-08
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!
0
Comment
Question by:larisa1970
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26147141
which database / version ? also is there any unique  key in the table
0
 

Author Comment

by:larisa1970
ID: 26147176
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
 
LVL 7

Expert Comment

by:technofile
ID: 26147229
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26147315
;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
 

Author Comment

by:larisa1970
ID: 26147359
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
 

Author Comment

by:larisa1970
ID: 26147425
Thank you, aneeshattingal.
That's exactly what I need
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question