Cumulative sum of a group by in SQL-CodePudding

I have a table that have just 2 columns month and sales, and I am trying to first sum the sales and group by month and next to it have a cumulative sum of the column sales, but I am getting an error in my SQL

With this query I can group by month and get the sum of sales by month

select month, sum(sales)
from sales
group by month

and with this query I can get the cumulative sum but is not grouped by month

select month
    , sum(sales) over(order by month) as cum_sum
from sales

Now how can I combine those 2 in 1 select statement that works?

So for this table

month   sales
1   100
1   200
2   100
2   100
3   100
3   50

I would get this result

month month_sales    cum_sum
1         300          300
2         200          500
3         150          650

CodePudding user response：

You can combine it like this

select month, 
       sum(sales) as month_sales,
       sum(sum(sales)) over (order by month)     as cum_sum
from   sales
group by month

db<>fiddle demo

Note : this works for SQL Server and MySQL. Tested on fiddle

CodePudding user response：

We can try to use a subquery to calculate total sales each month before sum window function.

SELECT t1.*,sum(total_sales) over(order by month) as cum_sum
FROM (
  select month, sum(sales) total_sales
  from sales
  group by month
) t1