Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: Cummulative Sum ussing a Query

    Hello,
    I have this query:

    Code:
    SELECT [table].[C&#243;digo de pozo], [table].Date, (SELECT Sum( [table 2].Producci&#243;n) FROM [table 2] WHERE  [table 2].Date<= [table].Date AND [table 2].[C&#243;digo de pozo]=[table].[C&#243;digo de pozo]) AS [Cummulative]
    FROM [table] INNER JOIN [table 2] ON ([table].[C&#243;digo de pozo] = [table 2].[C&#243;digo de pozo]) AND ([table].Date = [table 2].Date);
    [table 2] is just the same as [table].

    I'm trying to make a cummulative sum of the "Producci&#243;n" (Production).

    Any ideas how to make this cummulative values work?

    Thank you in advance!
    Saludos,
    pepemosca

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a report with a running sum as opposed to doing it within the query itself.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2008
    Posts
    189
    Teddy, can you say that again?
    I don't need a report. I just need a query with the data.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Are you sure?

    What do you intend to do with the data once you've run the query?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Could you not carry out the inner select as a query on its own ? This produces a virtual table which can be included in the outer query joined to the other tables. In effect, all you are doing is to force the internal selection to be done first.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you give us a bit more on the table structure? For the running sum to have meaning, surely it needs an order by clause?
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    For the running sum to have meaning, surely it needs an order by clause?
    Isn't that what the Theta join is for?

    1) I would do this in a report too if possible.
    2) You know how much I love set based but this is a requirement that scales better when performed procedurally rather than in SQL.
    3) Hard to fix without the OP actually saying what the result of the existing query actually is and how that differs from the required result.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Correct poots; I phrased it wrong... But there needs to be an order to the resultset, there does not need to be an order by clause.
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Isn't that what the Theta join is for?

    1) I would do this in a report too if possible.
    2) You know how much I love set based but this is a requirement that scales better when performed procedurally rather than in SQL.
    3) Hard to fix without the OP actually saying what the result of the existing query actually is and how that differs from the required result.
    running totals in reports are not done procedurally.... they are (or at least were last time I checked) done by SQL aggregate queries, hence why theres always a performance hit in using the tempting text boxes = sum(myfield). however if you are using the domain statistical functions eg Average, StdDev etc.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I meant that if reports are not an option then rather than a SQL statement a procedural (perhaps using recordsets for example) solution might be quicker. Depends on the number of rows.

    Although reports accept expressions that are common to SQL statements I don't know that the underlying processing is necessarily set based. I don't know that it isn't either. Conclusion - I just don't know.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Couple of set based solutions
    Code:
    SELECT a.order_date
         , Sum(b.value) As [running_total]
    FROM   my_table a
     INNER
      JOIN my_table b
        ON a.order_date >= b.order_date
    GROUP
        BY a.order_date
    ORDER
        BY a.order_date
    
    
    
    SELECT a.order_date
         , a.value
         , (SELECT Sum(b.value)
            FROM   my_table b
            WHERE  b.order_date <= a.order_date
           ) As [running_total]
    FROM   my_table a
    ORDER
        BY a.order_date
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take special care with the above queries - if the order_date is not unique, you will get some interesting results... (hence why I asked for more information about your tables )
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2008
    Posts
    189

    Post

    Hello,
    Thank you all for your comments.

    To continue, here is the table structure:

    Code:
    Group1	Product	Production	Date
    A	P1	805	01/01/2008
    A	P1	2176	01/01/2009
    A	P1	880	01/01/2010
    A	P1	683	01/01/2011
    A	P1	253	01/01/2012
    A	P1	344	01/01/2013
    A	P1	111	01/01/2014
    A	P1	49	01/01/2015
    A	P1	18	01/01/2016
    A	P1	63	01/01/2017
    A	P1	17	01/01/2018
    A	P1	29	01/01/2019
    A	P2	793	01/01/2008
    A	P2	410	01/01/2009
    A	P2	757	01/01/2010
    A	P2	117	01/01/2011
    A	P2	56	01/01/2012
    A	P2	419	01/01/2013
    A	P2	188	01/01/2014
    A	P2	134	01/01/2015
    A	P2	51	01/01/2016
    A	P2	106	01/01/2017
    A	P2	5	01/01/2018
    A	P2	2	01/01/2019
    B	P1	299	01/01/2008
    B	P1	32	01/01/2009
    B	P1	995	01/01/2010
    B	P1	301	01/01/2011
    B	P1	440	01/01/2012
    B	P1	217	01/01/2013
    B	P1	259	01/01/2014
    B	P1	213	01/01/2015
    B	P1	67	01/01/2016
    B	P1	103	01/01/2017
    B	P1	28	01/01/2018
    B	P1	26	01/01/2019
    B	P2	499	01/01/2008
    B	P2	2016	01/01/2009
    B	P2	495	01/01/2010
    B	P2	349	01/01/2011
    B	P2	56	01/01/2012
    B	P2	427	01/01/2013
    B	P2	38	01/01/2014
    B	P2	66	01/01/2015
    B	P2	4	01/01/2016
    B	P2	142	01/01/2017
    B	P2	16	01/01/2018
    B	P2	19	01/01/2019
    Hope that helps.
    As you can see, I have to grouping columns. Group1 and Product.

    Then I need a column with the cummulative sum of the Production ordered by Date.

    Thank you all for your comments.
    Saludos,
    pepemosca

  14. #14
    Join Date
    Apr 2008
    Posts
    189
    Quote Originally Posted by georgev
    Couple of set based solutions
    Code:
    SELECT a.order_date
         , Sum(b.value) As [running_total]
    FROM   my_table a
     INNER
      JOIN my_table b
        ON a.order_date >= b.order_date
    GROUP
        BY a.order_date
    ORDER
        BY a.order_date
    
    
    
    SELECT a.order_date
         , a.value
         , (SELECT Sum(b.value)
            FROM   my_table b
            WHERE  b.order_date <= a.order_date
           ) As [running_total]
    FROM   my_table a
    ORDER
        BY a.order_date
    How do I use this part "my_table a".

    a, is a parameter?
    my_table, is the table name?

    I only have one table.

    Thanks!

  15. #15
    Join Date
    Apr 2008
    Posts
    189
    Works!

    Code:
    SELECT 
      a.Group1,
      a.[Product],
      a.Date,
      a.[Production],
      (SELECT
         Sum(b.[Production])
       FROM
         [ts2 - Hist&#243;rico de Production] b
       WHERE
         b.Date <= a.Date AND
         b.Group1 = a.Group1 AND
         b.[Product] = a.[Product]
      ) As [Production Acumulada]
    FROM
      [ts2 - Hist&#243;rico de Production] a
    ORDER BY
      a.Group1,
      a.[Product],
      a.Date;
    But it's a little slow.
    My table has 250,000 records.

    Any suggestions about how to make it faster?

    Thank you all!!!

Posting Permissions

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