This query is for a graph, I take the count () of the records that are greater, equal or less than a certain amount, but the field of Media, I need to be dynamic, that is to say that each time the query is made perform, calculate the average of the total amounts of records.
Annex code.
SELECT
COUNT(CASE WHEN DIFERENCIA_MINUTOS > MEDIA THEN 1 END) MENOR,
COUNT(CASE WHEN DIFERENCIA_MINUTOS = MEDIA THEN 1 END) IGUAL,
COUNT(CASE WHEN DIFERENCIA_MINUTOS < MEDIA THEN 1 END) MAYOR,
COUNT(1) TOTAL,
MEDIAN(DIFERENCIA_MINUTOS) MEDIA
FROM(
SELECT
(TRUNC(MOD((FECHA_1 - FECHA_2) * 24, 24))*60) + TRUNC(MOD((FECHA_1 - FECHA_2) * (60 * 24), 60)) DIFERENCIA_MINUTOS
FROM(
SELECT DISTINCT
A.CREATED FECHA_1,
B.CREATED FECHA_2
FROM TABLA_1 A
LEFT JOIN (SELECT CREATED FROM TABLA_2) B ON B.ID = A.ID
WHERE 1=1
)
)
Thank you.