CODEWITHSUNDEEP
mysqlsql
user1072337
user1072337

Reputation: 12935

Cumulative Sum in SQL

I am trying to run a query that gets the cumulative sum of a column in one of my tables. It looks like this:

set @csum := 0;
select order_date, Amount, (@csum := @csum + Amount) as cumulative_sum
from Orders
order by order_date

However, when running this, I get all NULLs for the cumulative_sum. Anything I"m doing wrong? Thanks!

Upvotes: 2

Views: 370

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220787

Since MySQL 8, a much better approach than using MySQL's vendor specific variables (and relying on the non-declarative nature of their calculation) is to use SQL standard window functions. Your query can be rewritten as such:

SELECT
  order_date,
  amount,
  sum(amount) OVER (ORDER BY order_date)
FROM orders
ORDER BY order_date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would guess that Amount could sometimes be NULL. Try this version:

select o.order_date, o.Amount,
       (@csum := @csum + coalesce(o.Amount, 0)) as cumulative_sum
from Orders o cross join
     (select @csum := 0) params
order by o.order_date;

Upvotes: 1

BrianAtkins
BrianAtkins

Reputation: 1349

I would suggest just doing:

select order_date, Amount, sum(Amount) as cumulative_sum
from Orders
order by order_date

If you need to store the value in a variable then you could do:

SELECT @Variable = sum(Amount)
from Orders

And maybe add a GroupBy if you wanted to sum the amounts per day

Upvotes: 1

Related Questions