Setting up running subtotals in a query

Posted on 2009-12-30
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!
Question by:larisa1970
    LVL 75

    Expert Comment

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

    Author Comment

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

    Expert Comment

    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.
    LVL 75

    Accepted Solution

    ;WITH CTE As
     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

    Author Comment

    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

    Author Comment

    Thank you, aneeshattingal.
    That's exactly what I need

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now