| |
|
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.
|
 |

09-08-10, 18:19
|
|
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.
|

09-08-10, 20:05
|
|
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.
|

09-08-10, 20:30
|
|
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?
|
|

09-08-10, 21:42
|
|
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
|
|

09-08-10, 23:12
|
|
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.
|
|

09-09-10, 08:57
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 5
|
|
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!!!
|
|

09-09-10, 13:03
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
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
|
|

09-10-10, 18:43
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 5
|
|
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
|
|

09-10-10, 22:17
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
I like it
Lenny shown to you the unique solution.
Kara
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|