Untitled

-- routes, sorted by quality index
SELECT gateway, avg(duration) / 60 as TQI, count(*) as count FROM (
    SELECT * FROM (
      ( SELECT gateways.description as gateway, calls.duration as duration
        FROM calls, gateways
        WHERE call_start > NOW() - INTERVAL 4 hour AND
              calls.id_route = gateways.id_route ) AS calls_answered
    )
    UNION ALL

    SELECT * FROM (
      ( SELECT gateways.description as gateway, 0 as duration
        FROM callsfailed, gateways
        WHERE call_start > NOW() - INTERVAL 4 hour AND
              callsfailed.id_route = gateways.id_route ) AS calls_failed
    )
) AS MainTable
GROUP BY (gateway)
ORDER BY TQI desc;