Jump to content

Running total query - almost working


Twitch

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.