I have two columns date and sales and the objective is to use case statement to create another column that shows the cumulative sum of sales for each date.

Here's the sales table

What would be the best way to write a case statement in order to meet the requirements below?

Desired output

CodePudding user response：

There's no need for a case statement here; you just need the SUM window function:

select date, sales, sum(sales) over (order by date, id)
from sales

(If date is unique, ordering by date is enough. If it is not, it is best practice to specify additional columns to order by to produce a non-arbitrary result, such as you would get if sometimes it considered rows with the same date in one order and sometimes in another.)

When you use the sum window function with no order by clause, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, causing it to sum the expression for all rows being returned. When you specify an order by, the default frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, summing the expression for all rows up through the current row in the given order, producing a cumulative total. Because this is the default, there is no need to specify it; if you do specify it, it goes after the ORDER BY in the window specification.

CodePudding user response：

You don't need a CASE expression, but rather just use SUM() as a window function:

SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS cumulative
FROM yourTable
ORDER BY date;