Running Sum partitioned by rows

Question & Answer
1

I’m having a bit of difficulty making a running sum calculation only calculate the aggregate of a row, rather than the entire table. Right now, the running sum calculates one row, then continues the calculation in the next row. My current running sum calculation goes along the lines of:

runningSum(sum({X1}),[truncDate(“MM”,{DATE})ASC],[Column Partititon, Row Partion - Not working as intended])

Any advice would be helpful, thank you!

2

Do you have a row number or a unique id for each row?

In that case you could partition by that as well and it should be unique.

2 Likes
3

The row is another field. I.e. Team: Red, Green, Blue. The column being Quarters in a year. So I should be able to partition it by color, and the running sum would only return the sum of the teams in individual rows?
I.e.
runningSum(sum({X1}),[truncDate(“MM”,{DATE})ASC],[Column Partititon, Row Partition])?

4

yes. To clarify, partitions will essentially group that columns value together. You can only partition by column values in calculated fields.

https://www.sqlshack.com/database-table-partitioning-sql-server/

2 Likes