how to do the average oracle sql

Question

how to do the average oracle sql

Navigation

#1 by (0 votes)

1

I need to make the average of the delays of a flight database.

I need to add it to the following query:

SELECT V.Id_Aeropuerto_Destino AS Id_Aeropuerto, C.Nombre AS Ciudad_Destino, A.nombre AS Aeropuerto,
(SELECT SUM((TO_NUMBER(V.Retraso_Salida,'99999999D99','nls_numeric_characters=''.,''')) + 
        (TO_NUMBER(V.Retraso_Llegada,'99999999D99','nls_numeric_characters=''.,''')))
        FROM Vuelo V
        WHERE V.Id_Aeropuerto_Destino=Id_Aeropuerto) Retraso_Total
FROM Ciudad C, Aeropuerto A, Vuelo V
WHERE V.Id_Aeropuerto_Destino=A.Id_Aeropuerto AND A.Ciudad = C.Id_Ciudad;

so that the complete query will take out

Id_aeropuerto, 
nombre_ciudad, 
nombre_aeropuerto, 
media_retraso de ese aeropuerto y 
total_retraso de ese aeropuerto.

I know it's done with AVG , but I do not know how.

thanks!

sql oracle

asked by roberX100 26.12.2016 в 14:49

source

1 answer

Is it possible to modify the content of the window.location without redirecting the page? [closed] Modify CSS file from Javascript

score 0 · Answer 1

I do not see the need to use a sub-query, and with your original design there will be many repeated rows (one for each flight) containing the same airport information.

I've rewritten your query to return a single line per airport, which I think is what you're looking for

select   a.id_Aeropuerto
       , c.Nombre Ciudad
       , a.Nombre Aeropuerto
       , avg(  to_number(v.Retraso_Salida, '99999999D99', 'nls_numeric_characters=''.,''')
             + to_number(v.Retrado_Llegada, '99999999D99', 'nls_numeric_characters=''.,''')
            ) Retraso_Promedio
       , sum(  to_number(v.Retraso_Salida, '99999999D99', 'nls_numeric_characters=''.,''')
             + to_number(v.Retrado_Llegada, '99999999D99', 'nls_numeric_characters=''.,''')
            ) Retraso_Total
  from Ciudad c
       inner join Aeropuerto a on c.id_ciudad = a.Ciudad
       inner join Vuelo v on v.id_Aeropuerto_Destino = a.id_Aeropuerto
 group by a.id_Aeropuerto
        , c.Nombre
        , a.Nombre

This is done by using the clause group by and the aggregation functions sum and avg , which will return the sum and the average delay time, respectively.