I have a table in a MySQL database that contains information about passenger flights. I want to do an analysis of flight delays. I would like to add a calculated attribute to each row that indicates how many flights that arrived at the airport of departure from the current row, by the same operator, were delayed in the last 24 hours.
I have not had the luck to write this query. I think I can express my intention with:
- Find a count of all the rows count (*) where - From the same carrier. a.FlightCarrier = thisRow.FlightCarrier - That arrived at our airport of departure. a.DestinationAirport = thisRow.OriginAirport - scheduled to arrive before ScheduledDepartureTimeUTC a.ScheduledArrivalTimeUTC < thisRow.ScheduledDepartureTimeUTC -- scheduled to arrive after (ScheduledDepartureTimeUTC - 24h) a.ScheduledArrivalTimeUTC > (thisRow.ScheduledDepartureTimeUTC - 24h) - had a delayed arrival a.DelayedArrival = 1
I tried to write this as a SQL statement:
alter table flight add column DelayedCarrierArrivals24H int as (select count (*) of the flight to where a.FlightCarrier = FlightCarrier and a.DestinationAirport = OriginAirport and a.ScheduledArrivalTimeUTC < ScheduledDepartureTimeUTC and a.ScheduledArrivalTimeUTC > (ScheduledDepartureTimeUTC - (60 * 60 * 24)) and a.DelayedArrival = 1) STORED;
However, MySQL resists this with "It has an error in its SQL syntax … near the selected count (*) of the flight to where a.FlightCarrier = FlightCarrier"
What is the correct way & # 39; to create this column in MySQL? Originally I had thought about using window functions, but I also could not guess the correct way to express my intention with those features.