CODEWITHSUNDEEP
mysql
AdrianBR
AdrianBR

Reputation: 2588

Mysql sum for period before date of row for each row

I have a table in mysql with the columns date, revenue, with the revenue generated in each day for the current month I need to turn it into the format date, 'sum of revenue until this date'

I could do it with a 

select max(date) as date, sum(revenue) as total_revenue_to_date from table where dayofmonth(date)<=1
union
select max(date) as date, sum(revenue) as total_revenue_to_date from table where dayofmonth(date)<=2
.......

etc, but would like to write it in a nicer format.

Anyone have any ideas?

asnwered: shortest, easiest to follow:

SELECT fulldate, 
(SELECT SUM(margin) FROM fact_agg_margin_live_daily d2 WHERE d1.fulldate>=d2.fulldate) AS margin 
FROM fact_agg_margin_live_daily d1

Upvotes: 0

Views: 1455

Answers (2)

Wallack
Wallack

Reputation: 792

I did some tests, so here's my example:

Table name = materiales Field name = id_material

Rows: 1, 2, 3, 4, 6, 7, 9 (of the column id_material)

Query used:

SELECT id_material, (SELECT SUM(id_material) FROM materiales M2 WHERE M1.id_material>=M2.id_material) AS suma FROM materiales M1

Expected result: column1 with the current id, column2 with the sum of the results of current id plus the previous one, and yep, it works.

You could change it to your current scenario somehow like this:

SELECT date, (SELECT SUM(revenue) FROM table T2 WHERE T1.date >= T2.date) AS revenue_until_current_date FROM table T1

This would return each date with the revenue until that date. If the rows are stored each day it would return every day with the revenue of that day plus the previous one.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

It would be much simpler to use group by for your query:

select max(date) as date, sum(revenue) as total_revenue_to_date
from table
group by dayofmonth(date)

You want the cumulative sum (which your query does not do). Here is one method (illustrated for a month with 30 days):

select max(date) as date, sum(revenue) as total_revenue_to_date
from t join
     (select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all
      select 5 as n union all select 6 union all select 7 union all select 8 union all select 9 union all
      select 10 as n union all select 11 union all select 12 union all select 13 union all select 14 union all
      select 15 as n union all select 16 union all select 17 union all select 18 union all select 19 union all
      select 20 as n union all select 21 union all select 22 union all select 23 union all select 24 union all
      select 25 as n union all select 26 union all select 27 union all select 28 union all select 29 union all
      select 30
     ) n
     on day(date) + n <= 30
group by day(date) + n;

You can also do this using a variable:

  select max(date) as date, sum(revenue) as total_revenue_to_date,
         @sum := @sum + SUM(revenue) as cumsum
  from table cross join
       (select @sum := 0) const
  group by dayofmonth(date)

Upvotes: 1

Related Questions