How to Calculate Cumulative Sum/Running Total in SQL Server in SQL Server

Let's say we want to see a hockey stick graph of our cumulative sessions by day in SQL Server. First, we'll need a table with a day column and a count column:

select
  convert(varchar(10), start_date, 105) as day,
  count(1)
from sessions
group by convert(varchar(10), start_date, 105);
     day    | count
------------+-------
 02-02-2020 | 3     
 03-02-2020 | 3     
 04-02-2020 | 4     

Next, we'll write a SQL Server common table expression (CTE) and use a window function to keep track of the cumulative sum/running total:

with data as (
  select
    convert(varchar(10), start_date, 105) as day,
    count(1)  as number_of_sessions
  from sessions
  group by convert(varchar(10), start_date, 105)
)

select
  day,
  sum(number_of_sessions) over (order by day asc rows between unbounded preceding and current row)
from data;
     day    | sum   
------------+-------
 02-02-2020 | 3     
 03-02-2020 | 6     
 04-02-2020 | 10    

Previous

Next

Ready for a modern SQL editor?