[RESOLVED] Running total query - almost working

This site is best viewed in a modern browser with JavaScript enabled.

[RESOLVED] 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

twitch

I wanted to return and post the code that worked in case anyone else needs something similar.

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