dontknow dontknow - 4 months ago 6
SQL Question

get value of columns in previous row and add to the next columns of the next row

I will create a graph of population by gender every year. And my graph should look something like this. enter image description here

But I'm having a hard time with the query.

Query

SELECT
year_added,
COUNT(case when gender='Male' then 1 end) as malecount,
COUNT(case when gender='Female' then 1 end) as femalecount,
COUNT(*) as totalcount
FROM tbl
WHERE status = 1
GROUP BY year_added


Result

enter image description here

In the result, 2016 male count is 4 and female count is 8. In 2017, I want the male count of 2016 to be added on the male count on 2017, meaning 2017 male count will be 5, same with female count and total count. I provided an image below of what the result should look like. Can you help me how to do this for me to proceed on doing the graph? Or is there any other way to achieve this?

enter image description here

Answer

Try this:

SELECT
    year_added,
    @malecount_v := @malecount_v + malecount as malecount,
    @femalecount_v := @femalecount_v + femalecount as femalecount,
    @totalcount_v := @totalcount_v + totalcount as totalcount
FROM (
    SELECT
       year_added,
       COUNT(case when gender='Male' then 1 end) as malecount,
       COUNT(case when gender='Female' then 1 end) as femalecount,
       COUNT(*) as totalcount
    FROM tbl
    WHERE status = 1
    GROUP BY year_added
    ORDER BY year_added
) t1
CROSS JOIN (SELECT @malecount_v := 0, @femalecount_v := 0, @totalcount_v := 0) t2
Comments