PopSQL

How to Calculate Cumulative Sum-Running Total in PostgreSQL

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

select
  date_trunc('day', created_at) as day,
  count(1)
from users
group by 1
         day         | count
---------------------+-------
 2018-01-01 00:00:00 |     10
 2018-01-02 00:00:00 |     10
 2018-01-03 00:00:00 |     10

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

with data as (
  select
    date_trunc('day', created_at) as day,
    count(1)
  from users
  group by 1
)

select
  day,
  sum(count) over (order by day asc rows between unbounded preceding and current row)
from data
database icon
Real-time SQL collaboration is here
Get started with PopSQL and PostgreSQL in minutes