How to Calculate Cumulative Sum/Running Total 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
database icon
Better SQL for the people
Get more done with PopSQL and SQL Server