Search code examples
sqlsum

Cumulative Sum When rows have the same value sql sql server

I'd like to do cumulative sum of the column SubTotal to get a cumulative RunningTotal column. But I noticed that if rows have the same values, the cumulative sum doesn't work. e.g. In the below example, the row 5 and row 6 have the same SubTotal value of 838.9178. Because of that, the RunningTotal column doesn't sum up for row 5 and row 6.

example

How can i fix this issue? I expect to get the results like below:

expected

Below please find my query. Many thanks in advance! 

select YEAR(OrderDate) as FiscalYear,
       REPLACE(CONVERT(varchar(12), OrderDate,1), ' ', '-') as OrderDate,
       SubTotal,
       SUM (SubTotal) OVER (partition by YEAR(OrderDate) ORDER BY OrderDate,SubTotal ) AS RunningTotal
from Sales.SalesOrderHeader
where YEAR(OrderDate)=2012 or YEAR(OrderDate)=2013
order by YEAR(OrderDate),OrderDate;
Solution

  • You need a row windowing clause or a stable sort. Try this:

       SUM(SubTotal) OVER (partition by YEAR(OrderDate) ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal

    Or, if you have a unique id (UniqueId for example) on the row:

       SUM(SubTotal) OVER (partition by YEAR(OrderDate) ORDER BY OrderDate, UniqueId) AS RunningTotal

    The issue is that the default windowing clause uses RANGE BETWEEN, which is not what you want.