Help with cumulative column that is grouped

  • I am trying to get a cumulative number for groups within a select statement. I have the cumulative working but only over the whole set of selected data. I need the cumulative column to reset for each PSC.

    SELECT PSC, WeekEnding, OppActualCount

    , ( SELECT SUM(c2.OppActualCount) FROM tblSalesPyramidDetails c2WHERE c2.RecNumId <= c1.RecNumId ) AS [OppActualCum]
    FROM tblSalesPyramidDetails c1

  • vmon (7/8/2009)


    I am trying to get a cumulative number for groups within a select statement. I have the cumulative working but only over the whole set of selected data. I need the cumulative column to reset for each PSC.

    SELECT PSC, WeekEnding, OppActualCount,

    ( SELECT SUM(c2.OppActualCount)

    FROM tblSalesPyramidDetails c2

    WHERE c2.PSC = c1.PSC

    AND c2.RecNumId <= c1.RecNumId ) AS [OppActualCum]

    FROM tblSalesPyramidDetails c1

  • Please keep in mind that those queries usually run into performance issues. It's a inline cursor using a triangular JOIN.

    Search this site for "running totals" to find some other discussions.

  • vmon (7/8/2009)


    I am trying to get a cumulative number for groups within a select statement. I have the cumulative working but only over the whole set of selected data. I need the cumulative column to reset for each PSC.

    SELECT PSC, WeekEnding, OppActualCount

    , ( SELECT SUM(c2.OppActualCount) FROM tblSalesPyramidDetails c2WHERE c2.RecNumId <= c1.RecNumId ) AS [OppActualCum]

    FROM tblSalesPyramidDetails c1

    If you'll take the time to post the table creation statement and some readily consumable data like the first link in my signature below explains, I'll show you how to do this in such a way that will blow the doors off of every other method possible. 😉

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

Viewing 4 posts - 1 through 3 (of 3 total)

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