Skip to content

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, count(p.numberofoutstanding)
from people p
left join outstandingpunches op
on p.fullname = op.fullname
group by

That outputs a table like this:

enter image description here

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.



You can use a cumulative sum. This looks like:

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

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.

6 People found this is helpful