Display correct cumulative_sum value using Mysql

Question

I used the mysql query below.

Select MONTHNAME(t.date) as month, YEAR(t.date) as iyear, t.income, 
    t.subincome, t.ssubincome, sum(rupees) as amount, 
    COALESCE((SELECT 
            SUM(x.rupees) 
                FROM house_details x WHERE x.id < t.id), '-') AS progressive_total, 
            (SELECT SUM(x.rupees) 
                FROM house_details x WHERE x.id <= t.id 
                group by MONTH(t.date)) AS cumulative_sum 
FROM house_details t 
WHERE YEAR(t.date) = YEAR(CURRENT_DATE()) AND t.subincome = "Garbage tax" 
GROUP BY MONTH(t.date) order by t.id

and got the value below:

month    iyear   income   subincome   amount   progressive_total   cumulative_sum
January  2017    Taxes    Garbage tax 385      -                   125
February 2017    Taxes    Garbage tax 890      125                 1015
March    2017    Taxes    Garbage tax 200      1015                1215
April    2017    Taxes    Garbage tax 250      1215                1465
May      2017    Taxes    Garbage tax 270      1465                1735

The cumulative_sum in first row shows wrongly as 125.It should show as 385.I want to display the correct cumulative_sum group by month. SQL Fiddle: http://sqlfiddle.com/#!9/c7b979/1/0

Answer 1

First thing you need to update is remove comparisons on id because id is not sequential as date January record can come after march or nay month record so this may result as invalid calculations. Second thing for cumulative sum and progressive total you need to do this when your main query is executed with GROUP BY like

SELECT *,
COALESCE((SELECT SUM(x.rupees) FROM house_details x WHERE MONTH(x.date) < t1.month AND x.subincome = "Garbage tax" ), 0) AS progressive_total, 
(SELECT SUM(x.rupees) FROM house_details x WHERE MONTH(x.date) <= t1.month AND x.subincome = "Garbage tax" )  AS cumulative_sum 
FROM (
    SELECT MONTHNAME(t.date) AS `monthname`,
    MONTH(t.date) `month`,
     YEAR(t.date) AS iyear,
     t.income,
     t.subincome,
     t.ssubincome,
     SUM(rupees) AS amount
    FROM house_details t 
    WHERE YEAR(t.date) = YEAR(CURRENT_DATE()) AND t.subincome = "Garbage tax" 
    GROUP BY MONTH(t.date) 
    ORDER BY t.date

 ) t1

Demo