Results 1 to 4 of 4

Thread: Subtotal in db2

  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Unanswered: Subtotal in db2

    I have joined 2 tables and the result of the query is as follows:
    I want to create 2 subtotal of the amount. One based on code and the other based on code and ULV.

    Code Symbol amount ULV
    JSCC JGBLF 8750680000 BO
    JSCC JGBLF 80000 BO
    JSCC JGBLF 720980000 BO
    JSCC JGBLF 8765880000 BO
    JSCC JGBLF 721190000 BO
    JSCC TOPIXF 3987710000 IX
    JSCC TOPIXF 49000000 IX

    My query is as follows:
    select y.code, y.symbol, y.amount, u.ULV from YEAR_mon y

    inner join ULV_code u on y.product = u.product

    where transaction_date=1101018 and

    order by y.code, y.symbol

    Im getting stuck in creating a subtotal.Can someone help!!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    the first part is really simple.
    Code:
    select y.code, y.symbol, sum(y.amount), u.ULV 
       from YEAR_mon y
    inner join ULV_code u
        on y.product = u.product
    where transaction_date=1101018
      and ???   -- you left this blank
    group by y.code, y.symbol, u.ULV
    order by y.code, y.symbol
    The above will give you your sub-total. It is more efficient to add those subtotals up in your presentation layer. Having said that, you can do it within your SQL by running the query again, in a nested expression to sum up at a different level.
    Dave nance

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can get two subtotals by specifying "GROUP BY GROUPING SETS ( code , (code , ulv) )".

    by the way,
    I have some questions.

    1-a) Do you want to get both of joined result(which you showed) and two subtotals?
    or
    1-b) Your final requirements are two subtotals only, and joined result you showed was a temporary result.

    2-a) If 1-a) was right.
    How to order whole result?
    joined result(which you showed) was ordered by code and symbol.
    while subtotals you want are "based on code" and "based on code and ULV".
    (symbol was not included.)

    2-b) If 1-b) was right.
    Can we ignore symbol in final result?

    3) Looking into your sample result,
    there is a one-to-one relationship between Symbol and ULV.
    Is it right?

    If the answer was "Yes",
    3-1) "subtotal based on code and ULV" would be same as "subtotal based on code and sumbol".
    Then you can get both of joined result and two subtotals ordered by code and symbol.
    Do you agree this?

    3-2) Is there any table having relationship between Symbol and ULV?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    3) Looking into your sample result,
    there is a one-to-one relationship between Symbol and ULV.
    Is it right?

    If the answer was "Yes",
    3-1) "subtotal based on code and ULV" would be same as "subtotal based on code and sumbol".
    Then you can get both of joined result and two subtotals ordered by code and symbol.
    Do you agree this?
    Although, the following query may not be exactly what you want,
    (because, you didn't show the values of product and other columns)
    you would get some hints to solve your problem.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     YEAR_mon(code, symbol, amount, product) AS (
    VALUES
      ('JSCC' , 'JGBLF'  , 8750680000 , 1)
    , ('JSCC' , 'JGBLF'  ,      80000 , 2)
    , ('JSCC' , 'JGBLF'  ,  720980000 , 3)
    , ('JSCC' , 'JGBLF'  , 8765880000 , 4)
    , ('JSCC' , 'JGBLF'  ,  721190000 , 5)
    , ('JSCC' , 'TOPIXF' , 3987710000 , 6)
    , ('JSCC' , 'TOPIXF' ,   49000000 , 7)
    )
    , ULV_code(product, ulv) AS (
    VALUES
      ( 1 , 'BO')
    , ( 2 , 'BO')
    , ( 3 , 'BO')
    , ( 4 , 'BO')
    , ( 5 , 'BO')
    , ( 6 , 'IX')
    , ( 7 , 'IX')
    )
    SELECT y.code
         , y.symbol
         , u.ulv
         , CASE
           WHEN GROUPING(y.symbol) = 1 THEN
                '**Total**'
           WHEN GROUPING(y.amount) = 1 THEN
                'Sub total'
           ELSE ''
           END  AS summary
         , SUM(y.amount) AS amounts
     FROM  YEAR_mon y
     INNER JOIN
           ULV_code u
       ON  y.product = u.product
    /*
     where transaction_date=1101018
       and ...
    */
     GROUP BY
           GROUPING SETS(  y.code
                        , (y.code , y.symbol , u.ulv)
                        , (y.code , y.symbol , u.ulv , y.product , y.amount)
                        )
     ORDER BY
           y.code
         , y.symbol
         , GROUPING(y.product)
    ;
    ------------------------------------------------------------------------------
    
    CODE SYMBOL ULV SUMMARY   AMOUNTS             
    ---- ------ --- --------- --------------------
    JSCC JGBLF  BO                      8750680000
    JSCC JGBLF  BO                           80000
    JSCC JGBLF  BO                       720980000
    JSCC JGBLF  BO                      8765880000
    JSCC JGBLF  BO                       721190000
    JSCC JGBLF  BO  Sub total          18958810000
    JSCC TOPIXF IX                      3987710000
    JSCC TOPIXF IX                        49000000
    JSCC TOPIXF IX  Sub total           4036710000
    JSCC -      -   **Total**          22995520000
    
      10 record(s) selected.

Posting Permissions

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