Can't get the cumulative sum(running total) within a group in SQL Server

Question

I'm trying to get a running total within a group but my current code just gives me an aggregate sum. For example, my data looks like this

  ID      ShiftNum   Status  Type      Rate      HourlyWage       Hours    Total_Amount     
 12542       1        Full    A         1           12.5            40        500
 12542       1        Full    A         1           12.5            35        420
 12542       2        Full    A         1            10             40        400
 12542       2        Full    B         1.2          10             40        480
 17842       1        Full    A         1            11             27        297
 17842       1        Full    B         1.3          11             30        429

And what I want is a running total within the same ID, Shift Number, and Status. For example, I want something like this as my final result

  ID      ShiftNum   Status  Type      Rate      HourlyWage       Hours    Total_Amount   Running_Tot  
 12542       1        Full    A         1           12.5            40        500            500
 12542       1        Full    A         1           12.5            35        420            920
 12542       2        Full    A         1            10             40        400            400
 12542       2        Full    B         1.2          10             40        480            880
 17842       1        Full    A         1            11             27        297            297
 17842       1        Full    B         1.3          11             30        429            726

However, my current code just gives me the total sum within each group. For example, 920, 920 for row 1&2. Here's my code.

Select a.*, 
      SUM(Hours) OVER (PARTITION BY ID, ShiftNum, Status ORDER BY ID, ShiftNum, Status) as Runnint_Tot
from table a

How do I fix my code to get the final result I want?

Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — JeffUK, Dec 01 '20 at 12:25
@alexsmith5123 . . . You are missing a unique identifier for each row. — Gordon Linoff, Dec 01 '20 at 12:28

score 0 · Accepted Answer · answered Dec 01 '20 at 12:26

You need an ordering column that uniquely defines each row. There is not an obvious one in your row, but something like this:

SUM(Hours) OVER (PARTITION BY ID, ShiftNum, Status ORDER BY hours) as Running_Tot

Or:

SUM(Hours) OVER (PARTITION BY ID, ShiftNum, Status
                 ORDER BY (SELECT NULL)
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) as Running_Tot

The problem you are facing is because the ORDER BY keys have ties. The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Note the RANGE. That means that all rows with ties are combined.

Also note that there is no utility to including the PARTITION BY keys in the ORDER BY (well . . . there is one exception in SQL Server if you don't care about the ordering, then including a key can be a handy short-cut). The ordering occurs within a partition.

If your rows can have exact duplicates, I would first suggest that you add a primary key. But, in the meantime, you could use:

with a as (
      select a.*,
             row_number() over (order by id, shiftnum, status) as seqnum
      from tablea a
     )
Select a.*, 
      SUM(Hours) OVER (PARTITION BY ID, ShiftNum, Status ORDER BY seqnum) as Running_Tot
from a;

The ordering will be arbitrary, but it will at least accumulate.

Can't get the cumulative sum(running total) within a group in SQL Server

1 Answers1