SQL – sum column for every date

This seemed like a very easy thing to do but I got stuck. I have a query like this:

select op.date, count(p.numberofoutstanding)
from people p
left join outstandingpunches op
on p.fullname = op.fullname
group by op.date

That outputs a table like this:

How can I sum over the dates so the sum for each row is equal to the sums up to that date? For example, the first column would be 27, the second would be 27 + 4, the third 27 + 4 + 11, etc.

I encountered this and this question, and I saw people are using OVER in their queries for this, but I’m confused by what do I have to partition. I tried partitioning by date but it’s giving me incorrect results.

Answer

You can use a cumulative sum. This looks like:

select op.date, count(*),
       sum(count(*)) over (order by op.date) as running_count
from people p join
     outstandingpunches op
     on p.fullname = op.fullname
group by op.date;

Note: I changed the join from a left join to an inner join. You are aggregating by a column in the second table. Your results have no examples of a NULL date column and that doesn’t seem useful. Hence, it seems that rows are assumed to match.