Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    8

    Unanswered: Accumulate function

    Hi,

    I have this query:

    SELECT

    DAN1,
    SUM(BDQTY) AS QTY_SOLD


    FROM GALF2.BD
    JOIN GALF2.DP ON DACAT = BDCAT

    WHERE (BDTOPN>=20130501)
    AND (BDCUST Between 510000 And 510999)


    GROUP BY DAN1
    ORDER BY QTY_SOLD DESC

    The result of this query is:
    DAN1 QTY_SOLD
    D 1440
    B 1290
    T 1180
    P3 990
    Q 554
    TP 310
    AC 280
    AD 230

    I would like to add addition column to accumulate the amounts in previous columns so the result will be like this:

    DAN1 QTY_SOLD QTY_SOLD_CUM
    D 1440 1440
    B 1290 2730
    T 1180 3910
    P3 990 4900
    Q 554 5454
    TP 310 5764
    AC 280 6044
    AD 230 6274

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try something like this (I'm not sure why, dbforums won't let me paste this statement as text, so I'll attach a picture).
    Attached Thumbnails Attached Thumbnails select.png  
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Subquery might be unnecessary, at least on DB2 for LUW.

    (2) "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is the default value, when ORDER BY clause was specified.
    Note 1: Please see Syntax diagram of aggregation-specification in
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

    So, it is not neccessary to specify the clause.
    Note 2: But, if you are not so familiar with OLAP specifications, it might be worth to consider to specify explicitly even if the values were default.


    As a conseuence this example might be worth to try.

    Example 1:
    Code:
    SELECT dan1
         , SUM(bdqty) AS qty_sold
         , SUM( SUM(bdqty) )
              OVER( ORDER BY SUM(bdqty) DESC
                  ) AS qty_sold_cum
     FROM  galf2.bd
     INNER JOIN
           galf2.dp
      ON   dacat = bdcat
     WHERE bdtopn >= 20130501
       AND bdcust BETWEEN 510000 AND 510999
     GROUP BY
           dan1
     ORDER BY
           qty_sold DESC
    ;

  4. #4
    Join Date
    Mar 2013
    Posts
    8
    Thanks for the prompt answer but I get the folowing error:

    [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ( was not valid. Valid tokens: , FROM INTO.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Oh, you are using DB2 for i5/OS.
    It doesn't support SUM(...) OVER(...), according to manual.
    It supports only RANK, DENSE_RANK, and ROW_NUMBER OLAP specifications.

    So, try something like...

    Example 2:
    Code:
    WITH
     sum_qty AS (
    SELECT dan1
         , SUM(bdqty) AS qty_sold
         , ROW_NUMBER()
              OVER( ORDER BY SUM(bdqty) DESC ) AS rnum
     FROM  galf2.bd
     INNER JOIN
           galf2.dp
      ON   dacat = bdcat
     WHERE bdtopn >= 20130501
       AND bdcust BETWEEN 510000 AND 510999
     GROUP BY
           dan1
    )
    SELECT dan1
         , qty_sold
         , (SELECT SUM(qty_sold)
             FROM  sum_qty AS c/*um*/
             WHERE c.rnum <= s.rnum
           ) AS qty_sold_cum
     FROM  sum_qty AS s/*um*/
     ORDER BY
           rnum ASC
    ;
    Last edited by tonkuma; 05-22-13 at 08:52. Reason: Replace "qty_sold DESC" in ORDER BY with "rnum ASC".

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example without using OLAP specifications.

    Example 3:

    Code:
    WITH
     sum_qty AS (
    SELECT dan1
         , SUM(bdqty) AS qty_sold
     FROM  galf2.bd
     INNER JOIN
           galf2.dp
      ON   dacat = bdcat
     WHERE bdtopn >= 20130501
       AND bdcust BETWEEN 510000 AND 510999
     GROUP BY
           dan1
    )
    SELECT dan1
         , qty_sold
         , (SELECT SUM(qty_sold)
             FROM  sum_qty AS c/*um*/
             WHERE c.qty_sold >  s.qty_sold
               OR  c.qty_sold =  s.qty_sold
               AND c.dan1     <= s.dan1
           ) AS qty_sold_cum
     FROM  qty_sold AS s/*um*/
     ORDER BY
           qty_sold_cum ASC
    /* or
           qty_sold DESC
         , dan1     ASC
    */
    ;

  7. #7
    Join Date
    Mar 2013
    Posts
    8
    Thanks a lot

Posting Permissions

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