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