Dear Team,
I have the sample data, from which I would like get the monthly count by year wise.
WITH t AS
(SELECT 1 AS ID, 'A' AS NAME, '01-Jan-1999' AS hired, 'CLERK' as Job
FROM DUAL
UNION ALL
SELECT 2, 'A', '01-Feb-1999','MGR'
FROM DUAL
UNION ALL
SELECT 3, 'B', '01-Jan-2000','CLERK'
FROM DUAL
UNION ALL
SELECT 4, 'C', '01-Feb-2000','CLERK'
FROM DUAL
UNION ALL
SELECT 5, 'D', '01-Mar-2000','STORE HEAD'
FROM DUAL
UNION ALL
SELECT 6, 'E', '01-Apr-2000','MGR'
FROM DUAL
UNION ALL
SELECT 7, 'F', '01-Jan-2001','STORE HEAD'
FROM DUAL
UNION ALL
SELECT 8, 'G', '01-Feb-2001','CLERK'
FROM DUAL)
SELECT *
FROM t;
Sample Out Put:
JOB Year Month COUNT(ID)
------------------------------------------------------------------------
CLERK 1999 Jan 1
CLERK 2000 Jan 1
CLERK 2000 Feb 1
MGR 1999 Feb 1
MGR 2000 Apr 1
S.H 2000 Mar 1
Thanks
---------------
Adding to the above sample one, Original report query is below. As per suggestion to get the output like above i need change the below one. Attached is the screen shot of this below query.
SELECT attribute1, fiscal_year, Type, COUNT(1) AS wo_count, RATIO_TO_REPORT(COUNT(1)) OVER() *100 AS Percent
FROM (SELECT a.attribute1, CASE WHEN EXTRACT(MONTH FROM a.schedule_date) IN (1, 2, 3) THEN EXTRACT(YEAR FROM a.schedule_date) ELSE 1+EXTRACT(YEAR FROM a.schedule_date) END AS fiscal_year, CASE WHEN a.site IN ('CAQ1', 'CS1') THEN 'CAMPUS' ELSE CASE WHEN a.site = 'TRANSPORT' THEN 'TRANSPORTATION' ELSE 'ACCOMMODATION' END END AS Type
FROM mis_wo_view a
WHERE wo_status NOT IN ('CANCELED', 'PND CANCEL') AND wo_type <> 'Standing Work Order' AND a.attribute1 IS NOT NULL)
GROUP BY attribute1, fiscal_year, Type
Please suggest.
Message was edited by: EV259