CODEWITHSUNDEEP
sqloracle-databaseselectjoin
thegreat078
thegreat078

Reputation: 83

How to combine several oracle select sql into one view?

I would like to have the results of these selects into one view. The common column is the RUNID, however the results from the right table is not always present. So it should return empty. I am thinking that all of these selects can be combined into one select, passing the RUNID to each of the counts for different criteria. 

--COUNT OF S16 ELIGIBLE ACCOUNTS
SELECT    PDDETAIL.RUNID, COUNT(*) AS "S16 ELIGIBLE ACCOUNTS"
FROM      PDDETAIL
LEFT JOIN PDSTAGES ON (PDDETAIL.PDID = PDSTAGES.PDID AND PDDETAIL.CUST_ID = PDSTAGES.CUST_ID)
WHERE     PDSTAGES.PDSTAGE = 'S16ELIGIBLE'
GROUP BY  PDDETAIL.RUNID
ORDER BY  PDDETAIL.RUNID ASC
;

--COUNT OF S16 MTO SUBMITTED
SELECT    PDDETAIL.RUNID,COUNT(*) AS "S16 MTO SUBMITTED"
FROM      PDDETAIL
LEFT JOIN PDSTAGES ON (PDDETAIL.PDID = PDSTAGES.PDID AND PDDETAIL.CUST_ID = PDSTAGES.CUST_ID)
WHERE     PDSTAGES.PDSTAGE = 'S16MTOREFRESHSUBMITTED'
GROUP BY  PDDETAIL.RUNID
ORDER BY  PDDETAIL.RUNID ASC
;

--COUNT OF S16 MTO RETURN FAILED
SELECT    PDDETAIL.RUNID,COUNT(*) AS "S16 MTO FAILED"
FROM      PDDETAIL
LEFT JOIN PDSTAGES ON (PDDETAIL.PDID = PDSTAGES.PDID AND PDDETAIL.CUST_ID = PDSTAGES.CUST_ID)
WHERE     PDSTAGES.PDSTAGE = 'S16MTORETURNFAILED'
GROUP BY  PDDETAIL.RUNID
ORDER BY  PDDETAIL.RUNID ASC
;

Upvotes: 1

Views: 263

Answers (1)

GuiGi
GuiGi

Reputation: 411

SELECT    
 PDDETAIL.RUNID,
 COUNT(CASE WHEN PDSTAGES.PDSTAGE = 'S16ELIGIBLE' THEN 1 END) AS "S16 ELIGIBLE ACCOUNTS",
 COUNT(CASE WHEN PDSTAGES.PDSTAGE = 'S16MTOREFRESHSUBMITTED' THEN 1 END) AS "S16 MTO SUBMITTED",
 COUNT(CASE WHEN PDSTAGES.PDSTAGE = 'S16MTORETURNFAILED' THEN 1 END) AS "S16 MTO FAILED"
FROM      PDDETAIL
LEFT JOIN PDSTAGES ON (PDDETAIL.PDID = PDSTAGES.PDID AND PDDETAIL.CUST_ID = PDSTAGES.CUST_ID)
GROUP BY  PDDETAIL.RUNID
ORDER BY  PDDETAIL.RUNID ASC;

Upvotes: 3

Related Questions