Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739

    easy SQL question: running total

    what's the best way to get a running sum in SQL?

    data is of the form:

    month, number
    1, 100
    2, 50
    3, 50
    4, 200
    5, 100
    etc.

    the return i want is:
    1, 100
    2, 150
    3, 200
    4, 400
    5, 500
    etc.


    izy
    currently using SS 2008R2

  2. #2
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    An interesting question. I've also came upon the same problem for a couple of times, and I am not sure that my solution was the best -- so I'll post it here to get criticized, and hopefully finally we'll get to an optimal solution. Like russians say, "truth is being born in disputes"

    I personally prefer doing everything inside a query rather than on a form, so the statement could look like
    select
    _____tblSource.Field1,
    _____tblSource.Field2,
    _____Sum(DSum("[Field2]","[tblSource]","[Field1]<=" & [Field1])) AS RunningTotal
    from
    _____tblSource
    group by
    _____tblSource.Field1,
    _____tblSource.Field2
    order by
    _____tblSource.field1;
    Sample data that I used in tblSource is
    Field1 Field2
    1 100
    2 50
    ... ...
    7 25
    etc.

    The minus of this approach would be that the Field1 values must always be sequential and you cannot sort the records otherwise than by Field1.

    However, on a form you can use a recordset and then address the .AbsolutePosition property of each record to obtain the current record position in a recordset. This allows relational approach to data but it limits running total usage to the form only, because if we store it in a table it immediately becomes non-relational, unless the table is never sorted otherwise -- but then again we get to approach #1...

    Anyway, it seems to me that using recordset and vb is better in this case.

    PS The _ marks are for readability, so just throw them away...
    Last edited by Virgo-Libra; 12-06-03 at 10:33.
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  3. #3
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    So, any comments?

    Eagerly awaiting criticism... Since the first solution is slow and the second requires tons of coding...
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Does it have to be SQL? The only way I can even think to approach this is with code - either Public *cough* variable (or a tmp table to hold the sum) and user-defined function or through a form and Module-Private *ahem* variable.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    What are you going to do with the datasource? You may be thinking to hard.. heh.. if you intend to use it for a report then life would be much easier doing the calculation after you've already pulled the dataset.

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I think Izy already knows the answer. I think he's just teasing us.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    Originally posted by HomerBoo
    I think Izy already knows the answer. I think he's just teasing us.
    I thought exactly the same, judging by his other posts

    But, back to the topic. I've tried several alternative approaches to creating a running sum by SQL and it really does seem that, for an SQL expression to work, it is necessary to know how the data is sorted, which would make it non-relational. MSDN suggests the same BTW

    So, with the second option... Anyone up to try to write some little code piece? I will try myself, just for fun, over this weekend. Intrigued to see what can I finally get to...

    PS I know that you can do this in a REPORT, but that is not so interesting
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it can be done with sql, and yes it requires a column to sequence on (that's obvious), and yes, it's fairly inefficient (because it uses a theta join where each row is joined to multiple rows)--
    Code:
    select t1.month, t1.[number], sum(t2.[number])
    from runningtotal t1
     , runningtotal t2
    where t1.month >= t2.month
    group by  t1.month, t1.[number]
    rudy
    http://r937.com/

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    yes it can be done with sql, and yes it requires a column to sequence on (that's obvious), and yes, it's fairly inefficient (because it uses a theta join where each row is joined to multiple rows)--
    Nerd.










    *No seriously, that's good stuff to know, thanks.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    no i wasn't teasing at all - i just didn't check this post for a few days (since virgo-libra's first reply).

    memo to brain: don't post questions over the weekend!


    this is a genuine problem.

    my data is a touch more complex than in the original question.

    tblData: month, number, product, company x 70,000 rows

    50 companies
    25 products in 4 groups defined by
    tblPgrp: pGroup, product

    data is 5 years history plus current year ( x 1,000 rows/month)

    i need to report for 50 companies x 4 prodGroups x CurrentYearMonths with a running total which gets sent to msGraph.

    i have HUMUNGOUS problems with the access:graph timing problems, and i need to run hundreds (not an exaggeration!) of doevents() to get the graphs out straight.

    my current strategy is divide and conquer:
    qry1: get current year group by month & product
    qry2: on qry1 group and sum by pGroup, Company
    then a couple of recordsets
    with companyRst
    with pGroupRst
    make table from Qry2 (edited: izy) with running sum 'temp table helps msGraph timing.
    'time out to accept that my running sum was clueless thrashing-around programming!
    next pGoupRst
    run the report to .SNP and save
    next companyRst
    e-mail all the .SNP

    this takes 40 minutes to run up to the e-mail step and touching anything on the PC trashes the run = 40 minutes coffee break.

    virgo-libra's first reply was too depressing: DSUM !
    50 cos x 4 groups x 12 months = ???? ...actually, HowMany DSUM's ?

    anyway, too many DSUMs on 70,000 rows.

    so: yes, it needs to be SQL cos it doesn't pass through a report but goes to msGraph.

    rudy's reply is already fascinating with the fabulous "theta join". wow. even if it doesn't work, the name is enough! i've got to try it.

    i dont have my A machine in front of me right now. ??? is runningtotal understood by A?

    thanks for the thoughts already expressed and any more that come out later.

    izy
    Last edited by izyrider; 12-09-03 at 14:30.
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2003
    Posts
    267
    izy

    Don't know if this will help you, but it is good to know it is there.

    Look at this MS knowledge Base Article - 207626

    http://support.microsoft.com/default...roduct=acc2000

    S-

  12. #12
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Gee Teddy,

    I hope you're actually acquainted with Rudy, because to the casual observer, it appears like he has a solution and you just have sarcasm.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    didn't bother me

    i took it as a compliment


  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    i'm trying my theta join right now.

    probably i'll muddle through, but where did t2 come from?

    izy
    Last edited by izyrider; 12-10-03 at 03:06.
    currently using SS 2008R2

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    here is a half-code half-SQL idea that occurred to me while i was struggling with t2 in my theta join.

    any comments?

    particularly any comments on likely performance vs the famous theta join running with
    a/ dumb .MDB backend over LAN
    b/ mySQL backend running the famous theta join



    using saved queries to make testing easy:

    query1:
    group by Month and sum(aNum)

    query2:
    SELECT Query1.Month, CalcRT([SumOfaNum]) AS RunningTotal
    FROM Query1
    ORDER BY Query1.Month

    in a module:
    global RunSoFar as double
    public function CalcRT(anyDouble as double) as double
    RunSoFar = RunSoFar + anyDouble
    CalcRT = RunSoFar
    end function

    in a form:
    private sub butGo_Click()
    RunSoFar = 0
    docmd.openquery ("Query2")
    end sub

    initially i had some odd results (sometimes getting the wrong [too high]answer for the first month, but with correct accumulation for subsequent months). any suggestions why?

    after a compact & repair this error seems to have gone away. i just ran 20,000 test loops without error.

    next step is some benchmarking on a large data set and an effort to either reproduce the error or convince myself it wont come back again.

    izy
    currently using SS 2008R2

Posting Permissions

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