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!
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
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;
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
@csum, it's probably going to be initialised as an INT, and 'Amount' is probably - what, decimal? Tryset @csum := 0.0;