Jump to content

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


Go to solution Solved by Barand,

Recommended Posts

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;

 

Link to post
Share on other sites
  • Solution

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;

 

  • Like 2
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.