#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    HELP ! Running Totals


    Wondering if anyone can help me out please as I can't simply get this to work! :-(

    I have two tables:
    fixeddays - tables with the date (dd/mm/yyyy) of every Friday in 2013
    purchases - table with Purchase Order information

    What I need:
    Every Friday I need to report what has been ordered/spent during the week and keep a Running Total.

    The Code:

    set @runtot=0;
    select
    t1.d,
    t1.wk,
    t1.tot,
    (@runtot := @runtot + t1.tot) as RT
    from
    (select
    fixeddays.day as d,
    week(fixeddays.day) as wk,
    sum(purchases.cost) as tot
    from fixeddays left join purchases on week(fixeddays.day) = week(purchases.datepurchase)
    group by week(fixeddays.day)) as t1

    Sample Result:

    Column RT is not keeping a running total...

    d wk tot RT
    ========== ==== ======== =======
    04/01/2013 0 NULL NULL
    11/01/2013 1 3000.00 NULL
    18/01/2013 2 2500.00 NULL
    25/01/2013 3 NULL NULL
    01/02/2013 4 1500.00 NULL
    08/02/2013 5 3000.00 NULL
    15/02/2013 6 2000.00 NULL
    22/02/2013 7 4500.75 NULL
    01/03/2013 8 6740.25 NULL

    Any help would be appreciated.

    Thanks. Josť Carlos
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    please explain why you couldn't do this in your application code (php or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    please explain why you couldn't do this in your application code (php or whatever)
    I'm somewhat of a newbie when it comes to PHP... I want to create a view in MySQL and then pass it to JasperReports... I'm also trying to figure out how to do running totals in JasperReports.

IMN logo majestic logo threadwatch logo seochat tools logo