Twitch Posted January 28, 2013 Share Posted January 28, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/273760-running-total-query-almost-working/ Share on other sites More sharing options...
Psycho Posted January 29, 2013 Share Posted January 29, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/273760-running-total-query-almost-working/#findComment-1408850 Share on other sites More sharing options...
Twitch Posted January 29, 2013 Author Share Posted January 29, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/273760-running-total-query-almost-working/#findComment-1408951 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.