Question 21192 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use a MySQL case statement in a window frame to calculate running total of a column?

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

date sales
2019-04-01 50
2019-04-02 100
2019-04-03 100

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

Desired output

date sales cumulative
2019-04-01 50 50
2019-04-02 100 150
2019-04-03 100 250
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search