CODEWITHSUNDEEP
sqloracle-databasegroup-by
Stikoun
Stikoun

Reputation: 105

SQL Use result of two SUM queries to one query

maybe this is simple question but I dont have idea how to do it. I have 2 SQL queris, both are almost the same they are different only in WHERE clause. Each query returns 1 SUM number. I need to get result from both query and use it to make 1 column called "RESULT" where will be counted the results of the queries.

1 QUERY: 

SELECT SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
        FROM ACCOUNTS a 
        INNER JOIN PARTIES p
        ON a.PT_KEY = p.PT_KEY
        INNER JOIN ACCOUNT_COLLATERALS c
        ON c.ACC_KEY = a.ACC_KEY
        INNER JOIN COLLATERALS co
        on c.COLT_KEY = co.COLT_KEY
        INNER JOIN COLLATERAL_PORTFOLIOS por
        ON co.COLTPTF_KEY = por.COLTPTF_KEY
        WHERE co.COLTPTF_KEY = '261' AND 
        co.COLT_DELETED_FLAG = 'N' AND 
        a.ACC_CLOSE_FLAG = 'N' 
        AND  co.COLT_SHORTENING_COEFFICIENT = 82

2 QUERY: 

SELECT SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
        FROM ACCOUNTS a 
        INNER JOIN PARTIES p
        ON a.PT_KEY = p.PT_KEY
        INNER JOIN ACCOUNT_COLLATERALS c
        ON c.ACC_KEY = a.ACC_KEY
        INNER JOIN COLLATERALS co
        on c.COLT_KEY = co.COLT_KEY
        INNER JOIN COLLATERAL_PORTFOLIOS por
        ON co.COLTPTF_KEY = por.COLTPTF_KEY
        WHERE co.COLTPTF_KEY = '261' AND 
        co.COLT_DELETED_FLAG = 'Y' AND 
        a.ACC_CLOSE_FLAG = 'Y' 
        AND  co.COLT_SHORTENING_COEFFICIENT = 82

Lets say first query return SUM value 10 and second 5. So I need to have query which will return column RESULT with 15.

Maybe there is some more effective way in writing this query because it is different only in WHERE clause in two parametrs.

Thanks for help

Upvotes: 0

Views: 246

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can easily combine the conditions:

SELECT SUM(a.ACC_NOMINAL_AMOUNT)
FROM . . .
WHERE co.COLTPTF_KEY = '261' AND 
      co.COLT_SHORTENING_COEFFICIENT = 82 AND
      (co.COLT_DELETED_FLAG, a.ACC_CLOSE_FLAG) IN ( ('N', 'N'), ('Y', 'Y') )

Upvotes: 1

GMB
GMB

Reputation: 222432

This should get the job done:

SELECT SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
FROM ACCOUNTS a 
INNER JOIN PARTIES p ON a.PT_KEY = p.PT_KEY
INNER JOIN ACCOUNT_COLLATERALS c ON c.ACC_KEY = a.ACC_KEY
INNER JOIN COLLATERALS co ON c.COLT_KEY = co.COLT_KEY
INNER JOIN COLLATERAL_PORTFOLIOS por ON co.COLTPTF_KEY = por.COLTPTF_KEY
WHERE 
    co.COLTPTF_KEY = '261' 
    AND (a.ACC_CLOSE_FLAG, co.COLT_DELETED_FLAG) IN ( ('Y', 'Y'), ('N', 'N') )
    AND  co.COLT_SHORTENING_COEFFICIENT = 82

You can get the 3 results at once using conditional aggregation:

SELECT 
    SUM(CASE WHEN a.ACC_CLOSE_FLAG = 'N' AND co.COLT_DELETED_FLAG = 'N' 
        THEN a.ACC_NOMINAL_AMOUNT END) AS RESULT_N,
    SUM(CASE WHEN a.ACC_CLOSE_FLAG = 'Y' AND co.COLT_DELETED_FLAG = 'Y' 
        THEN a.ACC_NOMINAL_AMOUNT END) AS RESULT_Y,
    SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
FROM ACCOUNTS a 
INNER JOIN PARTIES p ON a.PT_KEY = p.PT_KEY
INNER JOIN ACCOUNT_COLLATERALS c ON c.ACC_KEY = a.ACC_KEY
INNER JOIN COLLATERALS co ON c.COLT_KEY = co.COLT_KEY
INNER JOIN COLLATERAL_PORTFOLIOS por ON co.COLTPTF_KEY = por.COLTPTF_KEY
WHERE 
    co.COLTPTF_KEY = '261' 
    AND (a.ACC_CLOSE_FLAG, co.COLT_DELETED_FLAG) IN ( ('Y', 'Y'), ('N', 'N') )
    AND  co.COLT_SHORTENING_COEFFICIENT = 82

Upvotes: 1

Related Questions