If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > sql problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 09-08-10, 18:19
MAP MAP is offline
Registered User
 
Join Date: Oct 2009
Posts: 5
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 18:23.
Reply With Quote
  #2 (permalink)  
Old 09-08-10, 20:05
tonkuma tonkuma is offline
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,193
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 20:10.
Reply With Quote
  #3 (permalink)  
Old 09-08-10, 20:30
MAP MAP is offline
Registered User
 
Join Date: Oct 2009
Posts: 5
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?
Reply With Quote
  #4 (permalink)  
Old 09-08-10, 21:42
tonkuma tonkuma is offline
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,193
Quote:
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
Reply With Quote
  #5 (permalink)  
Old 09-08-10, 23:12
tonkuma tonkuma is offline
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 2,193
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.
Reply With Quote
  #6 (permalink)  
Old 09-09-10, 08:57
MAP MAP is offline
Registered User
 
Join Date: Oct 2009
Posts: 5
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!!!
Reply With Quote
  #7 (permalink)  
Old 09-09-10, 13:03
Lenny77 Lenny77 is offline
Registered User
 
Join Date: Jul 2009
Location: NY
Posts: 886
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:

Quote:
MIN_NUM......MAX_NUM......SUM_NUM.......MIN_MO.... MAX_MO
2............... 40......................127........... 6..............2
Lenny
Reply With Quote
  #8 (permalink)  
Old 09-10-10, 18:43
MAP MAP is offline
Registered User
 
Join Date: Oct 2009
Posts: 5
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
Reply With Quote
  #9 (permalink)  
Old 09-10-10, 22:17
DB2Plus DB2Plus is offline
Registered User
 
Join Date: Jul 2009
Posts: 150
Exclamation I like it

Lenny shown to you the unique solution.

Kara
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On