Hi All,
I currently have the following SQL Statement:
SELECT B.MDA_LEV3_DEPT_NAME AS "EVP Area",
COUNT(CASE Z.MDA_EPAF_STATUS WHEN 'C' THEN 1 END) AS Approved,
COUNT(CASE Z.MDA_EPAF_STATUS WHEN 'A' THEN 1 END) AS "In Progress",
COUNT(CASE Z.MDA_EPAF_STATUS WHEN 'E' THEN 1 END) AS Draft
FROM PS_MDA_EPAF_RQST Z,
PS_MDA_EMPLOYEES B
--PS_MDA_EPAF_WFXREF T
WHERE MDA_EPAF_STATUS IN ('C','A','E')
--AND T.ORIGINATORID = Z.USERID
AND Z.USERID = B.EMPLID
AND Z.HRS_SUBMITTED_DTTM between to_date(:d1, 'dd-mon-yy' )
and to_date(:d2, 'dd-mon-yy' )
--AND Z.TRANSACTION_NBR = T.TRANSACTION_NBR
GROUP BY B.MDA_LEV3_DEPT_NAME
ORDER BY B.MDA_LEV3_DEPT_NAME
Which produces the following results:
| EVP Area | Approved | In Progress | Draft |
| Academic Affairs Sr VP Office | 4 | 0 | 0 |
| Cancer Medicine | 30 | 1 | 2 |
| Cancer Medicine - Research | 41 | 1 | 1 |
| Cancer Prev & Pop Sciences | 3 | 0 | 0 |
| Cancer Prev & Pop Sciences-Res | 1 | 0 | 0 |
| Diagnostic Imaging | 2 | 0 | 0 |
| Information Services | 16 | 14 | 0 |
| Internal Medicine | 4 | 1 | 0 |
| Pathology/Lab Medicine | 2 | 0 | 0 |
| Pathology/Lab Medicine - Resch | 1 | 0 | 0 |
| Pediatrics | 11 | 0 | 1 |
| Provost and EVP Departments | 1 | 0 | 0 |
| Radiation Oncology | 26 | 0 | 25 |
| Radiation Oncology - Research | 2 | 0 | 0 |
| Regulatory Affairs | 1 | 0 | 0 |
| Surgery | 4 | 0 | 0 |
| Vice Provost Clinical Researc | 1 | 0 | 0 |
| Vice Provost Science | 39 | 1 | 1 |
Goal: I want to add an additional column to show the total for each EVP area and I want to add a final row at the bottom to show the totals for Approved, In Progress, and Draft. Any help is much appreciated, thank you for your time.
Trey