Results 1 to 9 of 9

Thread: sql problem

  1. #1
    Join Date
    Oct 2009
    Posts
    7

    Unanswered: sql problem

    As an example, I have the following db2 table:
    Table = TESTB
    MO NUM
    1 8
    2 40
    3 16
    4 25
    5 36
    6 2

    I need to find the following values in a single query:
    min(num) = 2
    max(num) = 40
    sum(num) = 127
    mo of the min(num) = 6
    mo of the max(num) = 2

    This gets all but the last two:
    [code]
    select min(num),
    max(num)
    sum(num)
    from textb
    [code]
    How do I get the mo related to the min(num) as well as the mo related to the max(num) incorporated into the same statement?
    Last edited by MAP; 09-08-10 at 19:23.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     testb(mo , num) AS (
    VALUES
      (1 ,  8)
    , (2 , 40)
    , (3 , 16)
    , (4 , 25)
    , (5 , 36)
    , (6 ,  2)
    )
    SELECT MIN(num) AS "min(num)"
         , MAX(num) AS "max(num)"
         , SUM(num) AS "sum(num)"
         , MAX(CASE num WHEN min_num THEN mo END) AS "mo of min(num):1"
         , MAX(CASE num WHEN max_num THEN mo END) AS "mo of max(num):1"
         , MAX(CASE rn_as WHEN 1 THEN mo END) AS "mo of min(num):2"
         , MAX(CASE rn_de WHEN 1 THEN mo END) AS "mo of max(num):2"
      FROM (SELECT mo , num
                 , MIN(num) OVER() AS min_num
                 , MAX(num) OVER() AS max_num
                 , ROW_NUMBER() OVER(ORDER BY num ASC ) AS rn_as
                 , ROW_NUMBER() OVER(ORDER BY num DESC) AS rn_de
              FROM testb
           ) s
    ;
    ------------------------------------------------------------------------------
    
    min(num)    max(num)    sum(num)    mo of min(num):1 mo of max(num):1 mo of min(num):2 mo of max(num):2
    ----------- ----------- ----------- ---------------- ---------------- ---------------- ----------------
              2          40         127                6                2                6                2
    
      1 record(s) selected.

    Show the result vertically.

    Example 2:
    Code:
    WITH
     testb(mo , num) AS (
    VALUES
      (1 ,  8)
    , (2 , 40)
    , (3 , 16)
    , (4 , 25)
    , (5 , 36)
    , (6 ,  2)
    )
    , query_result AS (
    SELECT MIN(num) AS "min(num)"
         , MAX(num) AS "max(num)"
         , SUM(num) AS "sum(num)"
         , MAX(CASE num WHEN min_num THEN mo END) AS "mo of min(num)"
         , MAX(CASE num WHEN max_num THEN mo END) AS "mo of max(num)"
      FROM (SELECT mo , num
                 , MIN(num) OVER() AS min_num
                 , MAX(num) OVER() AS max_num
              FROM testb
           ) s
    )
    SELECT 
           CASE k
           WHEN 1 THEN
                'min(num) = ' || VARCHAR("min(num)")
           WHEN 2 THEN
                'max(num) = ' || VARCHAR("max(num)")
           WHEN 3 THEN
                'sum(num) = ' || VARCHAR("sum(num)")
           WHEN 4 THEN
                'mo of the min(num) = ' || VARCHAR("mo of min(num)")
           WHEN 5 THEN
                'mo of the max(num) = ' || VARCHAR("mo of max(num)")
           END
             AS "<expression> = <value>"
     FROM  query_result
     CROSS JOIN
           (VALUES 1,2,3,4,5) k(k)
     ORDER BY
           k
    ;
    ------------------------------------------------------------------------------
    
    <expression> = <value>          
    --------------------------------
    min(num) = 2                    
    max(num) = 40                   
    sum(num) = 127                  
    mo of the min(num) = 6          
    mo of the max(num) = 2          
    
      5 record(s) selected.
    Last edited by tonkuma; 09-08-10 at 21:10.

  3. #3
    Join Date
    Oct 2009
    Posts
    7

    OLAP Not Available

    Thanks for your response. It appears you are using OLAP functions which makes things so much easier. However, OLAP funcions are not available in our DB2 environment. Is there another way to accomplish this without using OLAP functions?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is there another way to accomplish this without using OLAP functions?
    scalar subselect.

    Example:
    MIN(num) OVER() AS min_num
    --->
    (SELECT MIN(num) FROM testb) AS min_num

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or, join

    An example:

    Code:
    WITH
     testb(mo , num) AS (
    VALUES
      (1 ,  8)
    , (2 , 40)
    , (3 , 16)
    , (4 , 25)
    , (5 , 36)
    , (6 ,  2)
    )
    SELECT MIN(num) AS "min(num)"
         , MAX(num) AS "max(num)"
         , SUM(num) AS "sum(num)"
         , MAX(CASE num WHEN min_num THEN mo END) AS "mo of min(num)"
         , MAX(CASE num WHEN max_num THEN mo END) AS "mo of max(num)"
      FROM testb
         , (SELECT MIN(num) AS min_num
                 , MAX(num) AS max_num
              FROM testb
           ) s
    ;
    ------------------------------------------------------------------------------
    
    min(num)    max(num)    sum(num)    mo of min(num) mo of max(num)
    ----------- ----------- ----------- -------------- --------------
              2          40         127              6              2
    
      1 record(s) selected.

  6. #6
    Join Date
    Oct 2009
    Posts
    7

    Thumbs up Works like a charm!!!!

    Thanks for your solution. This works great!! I now need to now apply this to a very large table. Hopefully, there will not be any performance issues since it appears to be reading the table twice. Thanks again!!!

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up DB2 made easy

    Much easier the following:

    Code:
    with TESTB (MO, NUM) as
    (select 1,  8
    from sysibm.sysdummy1 union all
    select 2, 40
    from sysibm.sysdummy1 union all
    select 3, 16
    from sysibm.sysdummy1 union all
    select 4, 25
    from sysibm.sysdummy1 union all
    select 5, 36
    from sysibm.sysdummy1 union all
    select 6,  2
    from sysibm.sysdummy1 ) 
    select 
    min(num) min_num, 
    max(num) max_num,
    sum(num) sum_num,
    INT(SUBSTR(min( digits(num) CONCAT CHAR(mo)),11)) min_mo, 
    INT(SUBSTR(max( digits(num) CONCAT CHAR(mo)),11)) max_mo 
    from TESTB
    And you'll get result:

    MIN_NUM......MAX_NUM......SUM_NUM.......MIN_MO.... MAX_MO
    2............... 40......................127........... 6..............2
    Lenny

  8. #8
    Join Date
    Oct 2009
    Posts
    7

    Thumbs up Thanks... Problem solved...

    Thanks a million for the responses!!!!
    All worked correctly when I applied them to my query.
    Since I am querying some fairly large tables and the complexity of the query itself is pretty significant, Lenny77's solution worked out best since I didn't have to read the table more than once.
    Thanks again. All the help is greatly appreciated.
    MAP

  9. #9
    Join Date
    Jul 2009
    Posts
    150

    Exclamation I like it

    Lenny shown to you the unique solution.

    Kara

Posting Permissions

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