View previous topic :: View next topic
|
Author |
Message |
bshkris
New User
Joined: 21 Mar 2005 Posts: 41 Location: pune
|
|
|
|
Hi,
I have table A. the data looks like below
Code: |
O_ID MSR_TYP_CD MSR_VAL_1_QT MSR_VAL_2_QT
1 BCCR19 1 23
2 BCCR19 1 24
3 BCCR19 0 23
4 BCCR19 0 23
|
Code: |
O_ID MSR_TYP_CD MSR_VAL_1_QT MSR_VAL_2_QT
1 BCCR20 1 1
56 BCCR20 4 45
33 BCCR20 0 0
45 BCCR20 0 0
|
Code: |
O_ID MSR_TYP_CD MSR_VAL_1_QT MSR_VAL_2_QT
56 BCCR21 6 0
67 BCCR21 1 0
44 BCCR21 1 0
1 BCCR21 1 1
|
the final result should be as below. the val1, val2 columns must have sum of values based upon the O_ID whether it has matching or not.
Code: |
O_ID MSR_TYP_CD MSR_VAL_1_QT MSR_VAL_2_QT
1 BCCR59 3 25
2 BCCR59 1 0
3 BCCR59 0 23
4 BCCR59 0 23
33 BCCR59 0 0
45 BCCR59 0 0
56 BCCR59 10 45
67 BCCR59 1 0 |
BCCR59 is new MSR TYP CD which is combination of BCCR19 + BCCR20 +BCCR21.
Please post your ideas and suggestion how we can do it in DB2 SQL.
Thanks.
CODE' D |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
You haven't described it well enough.
In your expected result
Where has O_ID 44 gone?
Why O_ID 2 has val1 and val2 as 1 & 0? If I understand it correctly, they should have been 1 & 24.
Why BCCR(19 + 20 + 21) is Not equal to BCCR(60) ?
Since your question is loosely built, I don't think many people would want to waste their time on this if you don't clarify.
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
Very poorly described, and you don't need to repeat 3 times the same table data to show us the sample, you could have posted in one shot the combinations.
See, if this simple sql make sense to what you ask.
Code: |
select
A.O_ID,
A.MSR1,
sum(A.MSR_VAL_1_QT),
sum(A.MSR_VAL_2_QT)
from
(
select
O_ID,
case when MSR_TYP_CD IN ('BCCR19','BCCR20','BCCR21') then 'BCCR59' else MSR_TYP_CD end as MSR1,
MSR_VAL_1_QT,
MSR_VAL_2_QT
from table) A
group by
A.O_ID,
A.MSR1 |
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
bshkris,
Your query pretty unclear.. Please clarify queries by Rahul.. Your output does not make sense to me atleast.. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
select O_ID , 'BCCR59', sum(msr_val_1_qt), sum(msr_val_2_qt)
from mytable
where msr_typ_cd in ('BCCR19','BCCR20','BCCR21')
group by O_ID |
|
|
Back to top |
|
|
|