1. Registered User
Join Date
Oct 2009
Posts
7

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. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Oct 2009
Posts
7

## 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. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

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. Registered User
Join Date
Oct 2009
Posts
7

## 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. Registered User
Join Date
Jul 2009
Posts
150

## 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
•