t100: route

Custom SQL query (hide)

SELECT
  year,
  origin_city_name,
  dest_city_name,
  c.name AS carrier,
  a.name AS aircraft,
  SUM(departures_performed) AS deps,
  SUM(departures_scheduled) AS scheduled,
  SUM(payload) AS total_payload,
  SUM(seats) AS total_seats,
  SUM(passengers) AS total_passengers,
  -- Calculate the true load factor for the whole year based on the new aggregated sums
  ROUND((1.0 * SUM(passengers)) / SUM(seats) * 100, 1) AS load_factor,
  SUM(freight) AS total_freight,
  '/t100/route_year?origin=' || origin || '&dest=' || dest || '&year=' || year || '&_hide_sql=1' as more_detail
FROM t100 AS t
  JOIN aircraft_types a ON t.aircraft_type = a.AC_TYPEID
  JOIN carriers c ON t.carrier_entity = c.CARRIER_ENTITY
WHERE
  origin = :origin
  AND dest = :dest
GROUP BY
  year,
  c.name,
  a.name
ORDER BY
  year DESC,
  a.name ASC,
  c.name ASC,
  total_passengers DESC