Query for data counting, using median () SQL Oracle

1

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.

    
asked by Ernesto Cruz 03.07.2018 в 21:09
source

0 answers