Skip to content
Advertisement

Results from multiple queries as one result in Oracle

I have results from same query with multiple conditions as shown below:

SELECT COUNT(DISTINCT CASEID) FROM MYTABLE WHERE YR=2019 AND STATUS IN('W') 
SELECT COUNT(DISTINCT CASEID) FROM MYTABLE WHERE YR=2019 AND STATUS IN('K') AND APSTAT='J'
SELECT COUNT(DISTINCT CASEID) FROM MYTABLE WHERE YR=2019 AND STATUS IN('L') AND RCODE='901'
SELECT COUNT(DISTINCT CASEID) FROM MYTABLE WHERE YR=2019 AND STATUS IN('L') AND
RCODE='910'

Advertisement

Answer

You could use conditional aggregation:

SELECT 
    COUNT(DISTINCT CASE WHEN STATUS IN('W')                 THEN CASEID END)
   ,COUNT(DISTINCT CASE WHEN STATUS IN('K') AND APSTAT='J'  THEN CASEID END)
   ,COUNT(DISTINCT CASE WHEN STATUS IN('L') AND RCODE='901' THEN CASEID END)
   ,COUNT(DISTINCT CASE WHEN STATUS IN('L') AND RCODE='910' THEN CASEID END)
FROM MYTABLE
WHERE YR=2019;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement