I'm stock with a SQL statement, about getting the right amount-CodePudding

I am so sorry if there is already an answer for this question, I just haven't been able to find it.

I am making a little chart to my home systems, which is a overview of my savings, I already have a chart showing how much is going in each months.

This is the SQL statement I have managed to get working so far

SELECT SUM(amount) AS total, DATE(dt) AS `date` FROM `savings` GROUP BY MONTH(`date`)

Which gives this result: SQL result image

But this is not fully what I am doing to get, since this statement gives an amount for each month, what I need is one that add to the last amount, so that I can see the full amount of saving over months, if that makes sense?

CodePudding user response：

It sounds like you want WITH ROLLUP:

SELECT SUM(amount) AS total, EXTRACT(YEAR_MONTH FROM dt) AS `yearmonth` 
FROM `savings` 
GROUP BY yearmonth WITH ROLLUP;

This does your grouping by year/month, plus adds one more row to the result set, with the total. The yearmonth column will be returned as NULL on that rollup row.