I have the following sql query which gives me the total h_time grouped by month, week and day. Instead I want the median h_time for month, week and day. How do I do that in Oracle SQL?
SELECT DAY,
MEDIAN(H_TIME) AS HANDLE_TIME
FROM(
select
MONTH, WEEK, DAY,
CASE
WHEN C.JOINED IS NOT NULL
THEN (NVL(C.TOTAL_TALK,0) + NVL(C.TOTAL_HOLD,0) + (NVL((C.DATETIME - C.START_DATETIME)*86400,0)) )/86400
ELSE 0 END AS H_TIME
from TABLE1 C
LEFT JOIN TABLE2 S
ON S.ID = C.ID
where c.direct = 'Inbound'
)
where UPPER(ITEM1) like 'SOMETHING%'
GROUP BY
DAY
OUTPUT:
DAY HANDLE_TIME
14-APR-17 .00567129629629629629629629629629629629629
15-APR-17 0
17-APR-17 0
17-APR-17 .00422453703703703703703703703703703703703
19-APR-17 .00269675925925925925925925925925925925925
19-APR-17 0
19-APR-17 0
19-APR-17 .00824074074074074074074074074074074074074
Your problem probably come from the time part that the DATE type carry (even if you don't explicitly set it).
To get rid of it you can use the trunc function.
Replace:
SELECT DAY,
By:
SELECT trunc(DAY)
And:
GROUP BY DAY
By:
GROUP BY trunc(DAY)