I have these data in 3 tables:

table 1: BU

table 2: STG_ACCOUNT

table 3: STG_CONTRACT

These 2 queries work fine:

Query 1:

SELECT
T2.BU_CODE, COUNT(T1.ACCOUNT_ID) AS COUNT_OF_ACCOUNT
FROM STG_ACCOUNT T1
FULL JOIN S_BU T2 ON T2.BU_CODE = T1.BU_CODE
WHERE T2.ARCHIVE_FLG = '1'
GROUP BY T2.BU_CODE
ORDER BY T2.BU_CODE;

Query 2:

SELECT
T2.BU_CODE, COUNT(T1.CONTRACT_ID) AS COUNT_OF_CONTRACT
FROM STG_CONTRACT T1
FULL JOIN S_BU T2 ON T2.BU_CODE = T1.BU_CODE
WHERE T2.ARCHIVE_FLG = '1'
GROUP BY T2.BU_CODE
ORDER BY T2.BU_CODE;

Now I would like to merge the result of these 2 queries to show a more elegant output:

What Oracle SQL function can help me?

CodePudding user response：

One option might be using CTE expressions

with x as 
( 
SELECT
T2.BU_CODE, COUNT(T1.ACCOUNT_ID) AS COUNT_OF_ACCOUNT
FROM STG_ACCOUNT T1
FULL JOIN S_BU T2 ON T2.BU_CODE = T1.BU_CODE
WHERE T2.ARCHIVE_FLG = '1'
GROUP BY T2.BU_CODE
ORDER BY T2.BU_CODE
), 
y as 
( 
SELECT
T2.BU_CODE, COUNT(T1.CONTRACT_ID) AS COUNT_OF_CONTRACT
FROM STG_CONTRACT T1
FULL JOIN S_BU T2 ON T2.BU_CODE = T1.BU_CODE
WHERE T2.ARCHIVE_FLG = '1'
GROUP BY T2.BU_CODE
ORDER BY T2.BU_CODE
) 
select x.bu_code , x.count_of_account, y.count_of_contract 
from x join y on x.bu_code=y.bu_code

CodePudding user response：

You can join both tables.

SELECT
T1.BU_CODE AS BU_CODE, COUNT(DISTINCT T2.ACCOUNT_ID) AS COUNT_OF_ACCOUNT, COUNT(DISTINCT T3.CONTRACT_ID) AS COUNT_OF_CONTRACT
FROM S_BU T1
LEFT JOIN STG_ACCOUNT T2 ON T1.BU_CODE = T2.BU_CODE
LEFT JOIN STG_CONTRACT T3 ON T1.BU_CODE = T3.BU_CODE
WHERE T1.ARCHIVE_FLG = '1'
GROUP BY T1.BU_CODE
ORDER BY T1.BU_CODE;