I m having a very tricky SQL question, maybe the trickiest I have had suppose I have one table below

called table_1

and now I need another table that sums up all the spend that I have made, before and include the every date. like to show every I have spend so far for each date.

And for each of the date.

Below is the table that I want compute

called table_2

I have tried with table_1 left join itself with left join on date_1 <= date_2.

does not work.

Could someone please give me a hint for it?

CodePudding user response：

You want a running total. You get this with the windw function SUM OVER.

select date, sum(spend) over(order by date) as sum_spent 
from mytable
order by date desc;

And if you should suffer from having to use an old MySQL version that doesn't support window functions yet, here is an old-fashioned alternative:

select 
  date, 
  (select sum(spend) from mytable t2 where t2.date <= t.date) as sum_spent 
from mytable t
order by date desc;

CodePudding user response：

Supplementary to Thorsten Kettner's answer, LEFT JOIN you've made attempt also works

SELECT a.date, SUM(b.spend)
FROM table_1 a
LEFT JOIN table_1 b ON a.date >= b.date
GROUP BY a.date