How to do running sum with group by in SQL?

Question

Now I have a table like as follows

    Date        Promotion campaign    Visit      Orders    Revenue
   2018-06-01    Promotion01           200         5       1000
   2018-06-01    Promotion02           250         10      1100
   2018-06-02    Promotion01           220         8       1350
   2018-06-03    Promotion03           300         15      2000  
    ...

And I would like to generate a table that shows the cumulative figures like

    Date         Visit        Orders       Revenue  
  2018-06-01      450           15           2100
  2018-06-02      670           23           3450
  2018-06-03      970           38           5450

I know first I may have to use the group by function and group the data by date, and afterwards how can I make the running sum for 3 columns?

The dw is amazon-redshift. Many thanks for your help in advance!

There are 2109 existing posts here using the search phrase [sql] running total, and 1212 using the search phrase [sql] running sum. None of those gave you enough information to at least make an attempt at solving it yourself? — Ken White
Yes, I made an attempt with group by and the lag function, but the figure is not correct and the task is a kind of in a rush, that's why I skipped in mentioning the trials made by myself and try to solve it asap before finding the potential error. But I understand you point — Pak Hang Leung

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-06-13T12:21:57

You can use aggregation and window functions:

select date, sum(visit) as visit, sum(orders) as orders,
       sum(revenue) as revenue,
       sum(sum(revenue)) over (order by date) as running_revenue
from t
group by date
order by date;

How to do running sum with group by in SQL?

1 Answers