[RESOLVED] Running total query - almost working
Results 1 to 2 of 2

Thread: [RESOLVED] Running total query - almost working

  1. #1
    Member
    Join Date
    Jan 2010
    Location
    Texas
    Posts
    49

    resolved [RESOLVED] Running total query - almost working

    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.

    Code:
    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

  2. #2
    Member
    Join Date
    Jan 2010
    Location
    Texas
    Posts
    49
    I wanted to return and post the code that worked in case anyone else needs something similar.

    Code:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •