Help with cumulative column that is grouped

Question

Help with cumulative column that is grouped

vmon

SSChasing Mays

Points: 624
More actions
July 8, 2009 at 1:10 pm

#103780

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

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

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

[KH] SSCommitted Points: 1908 More actions · Answer 1

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

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)