SQL in merging two tables and create the summation from fields in both tables, group by new field

a) There are 2 tables: CDR and CR. I need to get sum nominal from CR and sum of OS from CDR

b) In table CDR, I will need to create the category field Which will then use for group by: PDT,TAG,ARR,BUCKET

c)

Table CDR:

ID     NOMINAL
1      800
2      700
3      900
3      1000
4      760
5      666

Table CR:

ID    POOL_CD     EXPOSURE_ATT     OS      METHOD     BS     ASSET
1     XT11        04               800     ADV        KKK    ZZZ
2     XO21        05               700     K          ABC    NNN
3     XT11        04               300     ADV        GGG    ZZZ
4     X022        04               200     ADV        HHH    ZZZ
5     XT14        05               100     ADV        GGG    ZZZ

Final Table:

PDT      EXPOSURE_ATT      TAG      ARR       BUCKET       OS     NOMINAL
T        04                ALM      1         LE60         1100   2700   (ID 1 AND 3)
T        05                CONV     1         MT90         100    666    (ID 5)
O        04                ALM      2         LE60         200    760    (ID 4)   

SELECT 
CASE WHEN SUBSTR(a.POOL_CD,2,1)='O' THEN 'OVERDRAFT'
    WHEN SUBSTR(a.POOL_CD,2,1)='T' THEN 'TERM_LOAN'
    WHEN (a.exposure_att) in ('04','H3','H4') THEN 'PRIORITY_SEC'
    ELSE 'MASS' END PDT,
CASE WHEN SUBSTR(a.POOL_CD,3,1)='0' THEN '0'
    WHEN SUBSTR(a.POOL_CD,3,1)='1' 开发者_JAVA百科THEN '1'
    WHEN SUBSTR(a.POOL_CD,3,1)='2' THEN '2'
    WHEN a.POOL_CD='POOL_MY_HL' THEN 'POOL' END ARR,
CASE WHEN SUBSTR(a.POOL_CD,4,1) in ('1','2') THEN 'LE60'
    WHEN SUBSTR(a.POOL_CD,4,1)='3' THEN 'LE70'
    ELSE 'MT90' END BUCKET,
CASE WHEN (a.exposure_att) in ('01','02','04','09','AB') THEN 'ALM'
    ELSE 'CONV' END TAG,
a.Exposure_att,
sum(a.OS) as OS,
sum(b.NOMINAL) as NOMINAL
FROM cdr a left join cr b
on a.ID=b.ID
WHERE a.METHOD = 'ADV' 
AND a.BS not in ('ABC','DEF')
AND a.ASSET = 'ZZZ'
GROUP BY PDT,A.exposure_att,TAG, ARR, BUCKET
ORDER BY PDT,a.exposure_att,TAG, ARR, BUCKET

HI, Finally I have tried it out of the above.

SELECT x.PDT,x.exposure_att,x.TAG,x.ARR,x.BUCKET,SUM(x.OS) OS,SUM(x.NOMINAL) NOMINAL  
FROM  
(  
    SELECT *   
    FROM  
    (SELECT a.ID,a.exposure_att,a.OS,a.METHOD,a.BS,a.ASSET,  
        CASE  
        WHEN SUBSTR(a.POOL_CD,7,1)='O' THEN 'OVERDRAFT'  
        WHEN SUBSTR(a.POOL_CD,7,1)='T' THEN 'TERM_LOAN'  
        WHEN (a.exposure_att) in ('04','H3','H4') THEN 'PRIORITY_SEC'  
        ELSE 'MASS_HL' END PDT,  
        CASE  
            WHEN SUBSTR(a.POOL_CD,8,1)='0' THEN '0'  
        WHEN SUBSTR(a.POOL_CD,8,1)='1' THEN '1'  
        WHEN SUBSTR(a.POOL_CD,8,1)='2' THEN '2'  
        WHEN a.POOL_CODE='POOL_MY_HL' THEN 'POOL' END ARR,  
        CASE  
            WHEN SUBSTR(a.POOL_CD,20,1) in ('1','2') THEN 'LE60'  
        WHEN SUBSTR(a.POOL_CD,20,1)='3' THEN 'LE70'  
        ELSE 'MT90' END BUCKET,  
        CASE  
        WHEN (a.exposure_att) in ('01','02','04','09','AB') THEN 'ALM'  
        ELSE 'CONV' END TAG  
    FROM CDR a) a  
    LEFT JOIN  
        (SELECT b.ID,b.NOMINAL  
        FROM CR b) b  
    ON a.ID=b.ID  
    WHERE a.METHOD = 'ADV' 
    AND a.BS_TYPE not in ('ABC','DEF')
    AND a.ASSET = 'ZZZ'
) x   
GROUP BY x.PDT,x.exposure_att,x.TAG, x.ARR, x.BUCKET  
ORDER BY x.PDT,x.exposure_att,x.TAG, x.ARR, x.BUCKET