Myujth

I would like to add up meter values from month to month. My data looks like this:

| Date | Energy_produced |
--------------------------------
| 2011-01-01 | 12 | 
| 2017-01-02 | 23 | 
| 2017-01-03 | 25 | 
...
| 2011-02-01 | 11 | 
| 2017-02-02 | 12 | 
| 2017-02-03 | 17 | 

The GROUP BY would result in the SUM of the values "Energy_produced" for each month but I would like the sum to be added to get:

| Month | Energy_produced_added_up |
--------------------------------------
| 2011-01 | 60 | 
| 2017-02 | 100 | 

I read a lot of articles and understood that I could use a variable:

set @result := 0;
SELECT 
 DATE_FORMAT(t.Datum_Maand, '%m') AS measurement_month, 
 DATE_FORMAT(t.Datum_Maand, '%Y') AS measurement_year,
 (@result := @result + SUM(t.Geg_Maand)) AS SUMME
FROM tgeg_maand AS t
GROUP BY measurement_year, measurement_month
ORDER BY measurement_year, measurement_month

With GROUP BY, the value is not added up. The statement above displays the same result as:

SELECT 
 DATE_FORMAT(t.Datum_Maand, '%m') AS measurement_month, 
 DATE_FORMAT(t.Datum_Maand, '%Y') AS measurement_year,
 SUM(t.Geg_Maand) AS SUMME
FROM tgeg_maand AS t
GROUP BY measurement_year, measurement_month
ORDER BY measurement_year, measurement_month

What am I doing wrong?

Thanks

You need to do the summing by month first and then sum the monthly values. For this you will need to make your original query into a subquery:

set @result := 0;
SELECT measurement_month, measurement_year,
 @result := @result + monthly_sum AS SUMME
FROM (SELECT DATE_FORMAT(t.Datum_Maand, '%m') AS measurement_month, 
 DATE_FORMAT(t.Datum_Maand, '%Y') AS measurement_year,
 SUM(t.Geg_Maand) AS monthly_sum
 FROM tgeg_maand AS t
 GROUP BY measurement_year, measurement_month) mm
ORDER BY measurement_year, measurement_month

Use this query

SELECT 
 DATE_FORMAT(t.Datum_Maand, '%m') AS measurement_month, 
 DATE_FORMAT(t.Datum_Maand, '%Y') AS measurement_year,
 SUM(t.Geg_Maand) AS SUMME
FROM tgeg_maand AS t
GROUP BY measurement_year, measurement_month
ORDER BY measurement_year, measurement_month