Looking for solution for how to execute SQL for running total...

[A JM]

I have the following SQL executing on MySQL 5.6 and need a single statement solution or another solution on how to get a single recordset??? This is a working SQL statement with the following results.

week	cumulative_A	cumulative_M	cumulative_E	cumulative_W 	
1 	0 	5 	24 	7
2 	0 	5 	35 	14
10 	0 	14 	54 	22
11 	0 	17 	54 	55
12 	0 	17 	62 	65
13 	0 	17 	68 	77
14 	0 	17 	77 	86

Essentially the SQL below executes in 2 statements, 1 for establishing variables and the other to populate them,  my webpage isn't able to deal with that.

Any ideas on how to address??

Thanks,

SET @csumA:= @csumM:= @csumE:= @csumW:=0;
select week, (@csumA := @csumA + A) as cumulative_A, (@csumM := @csumM + M) as cumulative_M, (@csumE := @csumE + E) as cumulative_E, (@csumW := @csumW + W) as cumulative_W
       from(

SELECT WEEK(s.date) week, 
	SUM(CASE WHEN s.user_id = 50 THEN s.points ELSE 0 END) AS A,
    SUM(CASE WHEN s.user_id = 51 THEN s.points ELSE 0 END) AS M,
    SUM(CASE WHEN s.user_id = 52 THEN s.points ELSE 0 END) AS E,
    SUM(CASE WHEN s.user_id = 53 THEN s.points ELSE 0 END) AS W
FROM users u, scores s, league l WHERE u.user_id = s.user_id AND l.league_id = s.league_id and l.league_name = 'Sunday League' AND year(s.date) = YEAR(sysdate()) GROUP BY  s.date ORDER BY s.date ASC) PTS;

 

[Barand]

If you want it in a single query, initialize the variables in a joined subquery

SELECT
     , (@csumA := @csumA + A) as cumulative_A
     , (@csumM := @csumM + M) as cumulative_M
     , (@csumE := @csumE + E) as cumulative_E
     , (@csumW := @csumW + W) as cumulative_W
FROM (

        SELECT WEEK(s.date) week, 
            SUM(CASE WHEN s.user_id = 50 THEN s.points ELSE 0 END) AS A,
            SUM(CASE WHEN s.user_id = 51 THEN s.points ELSE 0 END) AS M,
            SUM(CASE WHEN s.user_id = 52 THEN s.points ELSE 0 END) AS E,
            SUM(CASE WHEN s.user_id = 53 THEN s.points ELSE 0 END) AS W
        FROM users u
            JOIN scores s ON u.user_id = s.user_id
            JOIN league l ON l.league_id = s.league_id
                          AND  and l.league_name = 'Sunday League' 
        WHERE year(s.date) = YEAR(sysdate()) 
        GROUP BY  s.date ORDER BY s.date ASC
    ) PTS
    JOIN (
           SELECT @csumA:=0, @csumM:=0, @csumE := 0, @csumW:=0
         ) INIT;

 

[A JM]

Many thanks for your quick solution..👍