Getting the number of analytic functions inside
I have a query that returns a record like.
Name Total_Case_Count User_Case_Count P_Count Rej_Count PPP_Count Active_Count
XYZ 20 10 05 02 01 02
I am using below query for this.
select row_number() over (order by result.USER_NAME asc) as row_index,
row_number() over (order by result.USER_NAME asc) as SERIAL_NO,
result.USER_NAME,
result.USER_ACCOUNT_ID,
MAX(Total_Case_Count) AS Total_Case_Count,
MAX(User_Case_COUNT) AS User_Case_COUNT,
MAX(Pending_Case_Count) AS Pending_Case_Count,
MAX(Rejected_Case_Count) AS Rejected_Case_Count,
MAX(Pending_For_Payment_Case_Count) AS Pending_For_Payment_Case_Count,
MAX(Active_Case_Count) AS Active_Case_Count
FROM
( SELECT
UA.USER_ACCOUNT_ID,
UA.FIRST_NAME AS USER_NAME,
NVL(PUIA.PARENT_USER_ACCOUNT_ID,C.CREATED_BY) PID,
COUNT(*) OVER () Total_Case_Count,
COUNT(*) OVER (PARTITION BY UA.USER_ACCOUNT_ID) User_Case_COUNT,
CASE
WHEN C.CASE_STATUS_ID = 2 THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Pending_Case_Count,
CASE
WHEN C.CASE_STATUS_ID = 4 THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Rejected_Case_Count,
CASE
WHEN C.CASE_STATUS_ID = 6 THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Pending_For_Payment_Case_Count,
CASE
WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Active_Case_Count
FROM CASE C
INNER JOIN CASE_STATUS CS ON CS.CASE_STATUS_ID = C.CASE_STATUS_ID
INNER JOIN SSO.PARENT_USER_IN_APPLICATION PUIA ON PUIA.APPLICATION_ID=12 AND PUIA.USER_ACCOUNT_ID=c.created_by
INNER JOIN SSO.USER_ACCOUNTS UA ON UA.USER_ACCOUNT_ID=C.CREATED_BY
INNER JOIN CASE_PARTY CP ON cp.sso_user_id=nvl(PUIA.PARENT_USER_ACCOUNT_ID,PUIA.USER_ACCOUNT_ID)
inner join sso.User_In_Types uit on uit.USER_ACCOUNT_ID = UA.USER_ACCOUNT_ID
inner join SSO.USER_TYPES ut on UT.USER_TYPE_ID = UiT.USER_TYPE_ID AND UT.APPLICATION_ID=12
where
UT.APPLICATION_ID = 12 and UT.USER_TYPE_ID = 2170
and UA.USER_ACCOUNT_ID = 2187150
and c.case_source not in (4)
) result
GROUP BY result.USER_NAME, result.USER_ACCOUNT_ID
ORDER BY USER_NAME
Pay attention to the Active_Case_Count column . it does not bring the status score that passes (1,3,5,6,7,8,9) within the case. It just returns the number of states of one case.
In fact, this statement does not return the count of all statuses
CASE
WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Active_Case_Count
Any suggestion is really appreciated.
source to share
Try using:
CASE
WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN 1 ELSE 0 end as Active_Case_Count
Instead:
CASE
WHEN C.CASE_STATUS_ID In (1,3,5,7,8,9) THEN COUNT(*) OVER (PARTITION BY C.CASE_STATUS_ID,UA.USER_ACCOUNT_ID) end as Active_Case_Count
and
SUM(Active_Case_Count) AS Active_Case_Count
Instead
MAX(Active_Case_Count) AS Active_Case_Count
This will calculate the total number of records in statuses (1,3,5,7,8,9)
source to share
I think you could replace your operators with the case when c.case_statis_id ... then count(*) over ...
following:
count(case when c.case_status_id = 2 then c.case_status_id end) over (partition by UA.USER_ACCOUNT_ID) Pending_Case_Count,
count(case when c.case_status_id = 4 then c.case_status_id end) over (partition by UA.USER_ACCOUNT_ID) Rejected_Case_Count,
count(case when c.case_status_id = 6 then c.case_status_id end) over (partition by UA.USER_ACCOUNT_ID) Pending_For_Payment_Case_Count,
count(case when c.case_status_id In (1,3,5,7,8,9) then c.case_status_id end) over (partition by UA.USER_ACCOUNT_ID) Active_Case_Count
The benefit is that your analytic functions will reduce the number of passes because you now only have two different sentences over()
, not three.
However, I guess you don't even need the analytic functions - you are making the group in the outer query, so why not make this work part of it? For example:.
select row_number() over (order by result.USER_NAME asc) as row_index,
row_number() over (order by result.USER_NAME asc) as SERIAL_NO,
result.USER_NAME,
result.USER_ACCOUNT_ID,
MAX(Total_Case_Count) AS Total_Case_Count,
COUNT(*) AS User_Case_COUNT,
count(case when result.case_status_id = 2 then result.case_status_id end) AS Pending_Case_Count,
count(case when result.case_status_id = 4 then result.case_status_id end) AS Rejected_Case_Count,
count(case when result.case_status_id = 6 then result.case_status_id end) AS Pending_For_Payment_Case_Count,
count(case when result.case_status_id In (1,3,5,7,8,9) then result.case_status_id end) AS Active_Case_Count
FROM
( SELECT
UA.USER_ACCOUNT_ID,
UA.FIRST_NAME AS USER_NAME,
NVL(PUIA.PARENT_USER_ACCOUNT_ID,C.CREATED_BY) PID,
c.case_status_id,
COUNT(*) OVER () Total_Case_Count
FROM CASE C
INNER JOIN CASE_STATUS CS ON CS.CASE_STATUS_ID = C.CASE_STATUS_ID
INNER JOIN SSO.PARENT_USER_IN_APPLICATION PUIA ON PUIA.APPLICATION_ID=12 AND PUIA.USER_ACCOUNT_ID=c.created_by
INNER JOIN SSO.USER_ACCOUNTS UA ON UA.USER_ACCOUNT_ID=C.CREATED_BY
INNER JOIN CASE_PARTY CP ON cp.sso_user_id=nvl(PUIA.PARENT_USER_ACCOUNT_ID,PUIA.USER_ACCOUNT_ID)
inner join sso.User_In_Types uit on uit.USER_ACCOUNT_ID = UA.USER_ACCOUNT_ID
inner join SSO.USER_TYPES ut on UT.USER_TYPE_ID = UiT.USER_TYPE_ID AND UT.APPLICATION_ID=12
where
UT.APPLICATION_ID = 12 and UT.USER_TYPE_ID = 2170
and UA.USER_ACCOUNT_ID = 2187150
and c.case_source not in (4)
) result
GROUP BY result.USER_NAME, result.USER_ACCOUNT_ID
ORDER BY USER_NAME;
(NB: I am assuming that UA.USER_ACCOUNT_ID is the primary key and that including USER_NAME in the group therefore does not change anything.)
ETA: unverified because you did not provide us with any sample data.
source to share