Cumulative Sum Over a Set of Rows in MySQL

How to calculate cumulative sums in MySQL

use this

select day,product_count,
sum(product_count) over (order by t.day ROWS UNBOUNDED PRECEDING) as cumulative_sum from (
SELECT
  date(purchase_date) as day,
  count(product_id) as product_count
  FROM products
  where day > DATE_SUB(now(), INTERVAL 6 MONTH)
  AND customer_city = 'Seattle'
  GROUP BY day 
  ORDER BY product_count desc
)t

Sum of previous rows in mysql

Frist, add a unique column to the table, Then you can use the same sql syntax that you found.

For example, Add the ID column(PRIMARY Key, auto increment)

+----+----------+------+
| ID | Time     | AR   |
+----+----------+------+
|  1 | 00:00:00 | 0.12 |
|  2 | 01:00:00 | 0.16 |
|  3 | 02:00:00 | 0.13 |
|  4 | 03:00:00 | 0.19 |
|  5 | 04:00:00 | 0.11 |
|  6 | 00:00:00 | 0.15 |
|  7 | 01:00:00 | 0.34 |
|  8 | 02:00:00 | 0.56 |
|  9 | 03:00:00 | 0.67 |
| 10 | 04:00:00 | 0.92 |
+----+----------+------+

the SQL:

set @arsum_1 := 0;
set @arsum_2 := 0;
set @arsum_3 := 0;

select t.Time, t.AR, ROUND(IFNULL(t1.A1, 0),2) as `1`, ROUND(IFNULL(t2.A2, 0), 2) as `2`, ROUND(IFNULL(t3.A3, 0),2) as `3` from time_sample t 
natural left join (select ID,  @arsum_1 := @arsum_1 + AR as A1 from time_sample where Time='01:00') as t1 
natural left join (select ID, @arsum_2 := @arsum_2 + AR as A2 from time_sample where Time='02:00') as t2 
natural left join (select ID, @arsum_3 := @arsum_3 + AR as A3 from time_sample where Time='03:00') as t3 order by t.Time;

output:

+----------+------+------+------+------+
| Time     | AR   | 1    | 2    | 3    |
+----------+------+------+------+------+
| 00:00:00 | 0.12 | 0.00 | 0.00 | 0.00 |
| 00:00:00 | 0.15 | 0.00 | 0.00 | 0.00 |
| 01:00:00 | 0.16 | 0.16 | 0.00 | 0.00 |
| 01:00:00 | 0.34 | 0.50 | 0.00 | 0.00 |
| 02:00:00 | 0.13 | 0.00 | 0.13 | 0.00 |
| 02:00:00 | 0.56 | 0.00 | 0.69 | 0.00 |
| 03:00:00 | 0.19 | 0.00 | 0.00 | 0.19 |
| 03:00:00 | 0.67 | 0.00 | 0.00 | 0.86 |
| 04:00:00 | 0.11 | 0.00 | 0.00 | 0.00 |
| 04:00:00 | 0.92 | 0.00 | 0.00 | 0.00 |
+----------+------+------+------+------+

MYSQL query group by and cumulative sum for financial orderbook

I use mysql 5.7 (since this version is using by me). you want to sum cummulative from the result of amount on each price, so you should use SUBQUERY in order to aggregation

Try this:

    set @CumulativeSum := 0;
    SELECT price, summ,
   (@CumulativeSum:= @CumulativeSum + summ) as cumsum 
      FROM (SELECT SUM(amount) as Summ
             FROM (SELECT * FROM orderbook) a
             GROUP BY price
             ORDER BY price) b

result

+-------+------+--------+
| price | summ | cumsum |
+-------+------+--------+
|    10 | 1.00 | 1.00   |
|    20 | 3.50 | 4.50   |
|    21 | 3.00 | 7.50   |
+-------+------+--------+

this is the fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4f607e38a23f069829dfaa177a8bc3d3

Cumulative sum with custom sorting in MySQL

First of all there are multiple entries per date. So the date alone doesn't suffice to get a stable sort order. I suggest ORDER BY expiry_date, id to get this straight.

Then, a running total would be done with window functions in any modern RDBMS. They are available in MySQL as of version 8.

select id, remaining, expiry_date, csum
from
(
  select
    id, remaining, expiry_date, 
    sum(remaining) over (order by expiry_date, id) as csum,
    sum(remaining) over (order by expiry_date, id 
                         rows between unbounded preceding and 1 preceding) as lag_csum
  from tickets
) summed
where coalesce(lag_csum, 0) < 215
order by expiry_date, id;

If window functions are not available, you can use a correlated aggregating subquery instead. That's probably much slower, but should work just as well.

select *
from
(
  select
    id, remaining, expiry_date,
    (
      select sum(remaining)
      from tickets t2
      where t2.expiry_date < t1.expiry_date
        or (t2.expiry_date = t1.expiry_date and t2.id <= t1.id)
    ) as csum,
    (
      select sum(remaining)
      from tickets t2
      where t2.expiry_date < t1.expiry_date
        or (t2.expiry_date = t1.expiry_date and t2.id < t1.id)
    ) as lag_csum
  from tickets t1
) summed
where coalesce(lag_csum, 0) < 215
order by expiry_date, id;

Both queries are standard SQL and are thus not restricted to MySQL.

Cumulative Sum in MySQL

You can achieve that in two steps: first of all get the sum for each year and month

select  concat(year(created_at), lpad(month(created_at), 2, '0')) as ye_mo,
        count(*) as cnt
from    users
group by concat(year(created_at), lpad(month(created_at), 2, '0'))

Then join it with itself, having each row matched with all previous ones

select  t1.ye_mo, sum(t2.cnt)
from    (
            select  concat(year(created_at), lpad(month(created_at), 2, '0')) as ye_mo,
                    count(*) as cnt
            from    users
            group by concat(year(created_at), lpad(month(created_at), 2, '0'))
        ) t1
join    (
            select  concat(year(created_at), lpad(month(created_at), 2, '0')) as ye_mo,
                    count(*) as cnt
            from    users
            group by concat(year(created_at), lpad(month(created_at), 2, '0'))
        ) t2
on      t1.ye_mo >= t2.ye_mo
group by t1.ye_mo
order by t1.ye_mo

Edit

The query above assumes you want the running sum to increase across different years. If you want to display the months only, and aggregate the values of different years in the same month, you can change id this way

select  t1.mnt, sum(t2.cnt)
from    (
            select  month(created_at) as mnt,
                    count(*) as cnt
            from    userss
            group by month(created_at)
        ) t1
join    (
            select  month(created_at) as mnt,
                    count(*) as cnt
            from    userss
            group by month(created_at)
        ) t2
on      t1.mnt >= t2.mnt
group by t1.mnt
order by t1.mnt

Finally, if you want the running sum to reset at the beginning of each year, you can do that like this

select  t1.yr, t1.mn, sum(t2.cnt)
from    (
            select  year(created_at) as yr, month(created_at) as mn,
                    count(*) as cnt
            from    userss
            group by year(created_at), month(created_at)
        ) t1
join    (
            select  year(created_at) as yr, month(created_at) as mn,
                    count(*) as cnt
            from    userss
            group by year(created_at), month(created_at)
        ) t2
on      t1.yr = t2.yr and
        t1.mn >= t2.mn
group by t1.yr, t1.mn
order by t1.yr, t1.mn

All three versions can be seen in action here

Rolling cumulative sum on MySQL query

Without variables:

 SELECT x.*
      , SUM(y.sales) 
   FROM my_table x 
   JOIN my_table y 
     ON y.date BETWEEN x.date - INTERVAL 3-1 DAY 
    AND x.date GROUP BY x.date;

If performance is an issue, then no doubt a variables-type solution will be forthcoming.

Create a Cumulative Sum Column in MySQL

If performance is an issue, you could use a MySQL variable:

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

Alternatively, you could remove the cumulative_sum column and calculate it on each query:

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

This calculates the running sum in a running way :)

---

Related Topics