Pass a Running Total query using CodeIgniter

Question

I am using CodeIgniter and trying run a query but it will not work. I believe that the error relates to the SET @runtot:=0; line. Here is the code:

<?php 
$qryRunningTotalRFRs = $this->db->query("
SET @runtot:=0;
SELECT
   q1.w,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
(SELECT week(IssuesFiledDate) AS w,
count(*) AS c
FROM tblappeals
WHERE tblappeals.Outcome = 'Upcoming' 
    AND tblappeals.`Year` = 2013  
    AND `IssuesFiledDate` >= '2013-03-31'
GROUP BY w
ORDER BY w ) 
AS q1
"); ?>

Can someone suggest a way to modify this so that I can pass this running sum query to MySQL? Thanks.

No, @runtot is just an abbreviation for 'running total'. I just realized that perhaps my answer below is not correct because I am not getting the results I expected. The running total is not being calculated. I guess I am back to the drawing board. — DanielAttard, Aug 05 '13 at 21:25

score 0 · Answer 1 · edited May 23 '17 at 12:11

I read the following comment on this question which said:

Don't miss the SET statement at the top to initialize the running total variable first or you will just get a column of NULL values.

For this reason, I believed that the SET statement was required for my query to work correctly. Problem was, the query would not run with the SET statement. I deleted the SET statement, and now it works fine. For whatever reason, I guess the SET statement is not required in this context.

Pass a Running Total query using CodeIgniter

1 Answers1