Running total query - almost working

[Twitch]

hey guys,

 

I am close to accomplishing a running total column but I think I am missing something simple. Any help would be greatly appreciated.

 

SET @runtot := 0;

SELECT
COUNT(adjustment_id) AS Adjustments,
DATE(FROM_UNIXTIME(shifts.outtime)) AS 'Month',
(@runtot := @runtot + COUNT(adjustment_id)) AS RT
FROM
adjustments
INNER JOIN shifts ON (
shifts.shiftID = adjustments.shiftID
)
INNER JOIN employees ON (shifts.idnum = employees.idnum)
WHERE
YEAR (FROM_UNIXTIME(shifts.outtime)) = '2012'
GROUP BY MONTH(FROM_UNIXTIME(shifts.outtime))
ORDER BY MONTH(FROM_UNIXTIME(shifts.outtime)) ASC

 

The code above outputs:

Adjustments | Month | RT

34 | 2012-08-29 | 34

161 | 2012-09-01 | 161

 

The RT matches the Adjustments and doesn't show the running total.

 

Thanks in advance,

Twitch

[Psycho]

I tried something similar (using COUNT to increment the dynamic var) on a simple table and got the same results. I'm guessing it has something with how the COUNT and GROUP BY operations and the order of precedence. I would suggest just handling that logic in the processing logic. However, I was able to make it work with a sub-query - but I think that would be inefficient.

 

SET @runtot := 0;

SELECT *,
      (@runtot := @runtot + Adjustments) AS RT
FROM (SELECT COUNT(adjustment_id) AS Adjustments,
            DATE(FROM_UNIXTIME(shifts.outtime)) AS 'Month'
     FROM adjustments
     INNER JOIN shifts
       ON (shifts.shiftID = adjustments.shiftID)
     INNER JOIN employees
       ON (shifts.idnum = employees.idnum)
     WHERE YEAR (FROM_UNIXTIME(shifts.outtime)) = '2012'
     GROUP BY MONTH(FROM_UNIXTIME(shifts.outtime))
     ORDER BY MONTH(FROM_UNIXTIME(shifts.outtime)) ASC
    ) AS sub

[Twitch]

Thanks for the reply, Psycho. I was able to get it with this:

 

SET @runtot := 0;


SELECT 
   Adjustments,
   Month,
   (@runtot := @runtot + Adjustments) AS RT
FROM ( SELECT
COUNT(adjustment_id) AS Adjustments,
DATE(FROM_UNIXTIME(shifts.outtime)) AS 'Month'
FROM
adjustments
INNER JOIN shifts ON (
shifts.shiftID = adjustments.shiftID
)
INNER JOIN employees ON (shifts.idnum = employees.idnum)
WHERE
YEAR (FROM_UNIXTIME(shifts.outtime)) = '2012'
GROUP BY MONTH(FROM_UNIXTIME(shifts.outtime))
ORDER BY MONTH(FROM_UNIXTIME(shifts.outtime)) ASC
) x