Oracle SQL – Summarize data based on values in one column

Please review the following sample data

App No Dept Status
123 IT Pending
124 Sales Closed
456 Sales Declined
821 IT Pending
221 IT Closed
876 Sales Pending
665 Sales Closed

Could you please suggest an oracle sql query that can provide the following output

Dept Closed Pendng Declined
IT 1 2 0
Sales 2 1 1

Thank you for your help.

>Solution :

WITH CTE(App_No,Dept,Status) AS
(
  SELECT 123,'IT','Pending' FROM DUAL UNION ALL
  SELECT 124,   'Sales' ,   'Closed' FROM DUAL UNION ALL
  SELECT 456,   'Sales' ,   'Declined' FROM DUAL UNION ALL
  SELECT 821,   'IT' ,  'Pending' FROM DUAL UNION ALL
  SELECT 221,   'IT' ,  'Closed' FROM DUAL UNION ALL
  SELECT 876,   'Sales' ,   'Pending' FROM DUAL UNION ALL
  SELECT 665,   'Sales' ,   'Closed' FROM DUAL
)
SELECT C.DEPT,
  SUM(CASE WHEN C.STATUS='Closed' THEN 1 ELSE 0 END) CLOSED,
  SUM(CASE WHEN C.STATUS='Declined' THEN 1 ELSE 0 END)DECLINED,
  SUM(CASE WHEN C.STATUS='Pending' THEN 1 ELSE 0 END)PENDING
  FROM CTE C
 GROUP BY C.DEPT
 ORDER BY C.DEPT

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=1d61ba161051cdff7ca7af622961ee2b

Leave a Reply