SQLize | PHPize | SQLtest

with t1 as (select f.departure_airport,f.flight_Id,f.aircraft_code, coalesce(count(bp.seat_no),0) as cnt1 from flights f join boarding_passes bp on f.flight_id = bp.flight_id join ticket_flights tf on tf.flight_id=bp.flight_id and bp.ticket_no=tf.ticket_no where extract('year' from f.actual_departure) = 2017 and extract('month' from f.actual_departure) = 8 and fare_conditions='Business' group by f.departure_airport,f.flight_Id,f.aircraft_code),t2 as(select aircraft_code,coalesce(count(seat_no),0) as cnt2 from seats where fare_conditions='Business' group by 1) select f.departure_airport, avg(coalesce(cnt1::decimal/cnt2*100,0))as average_business_occupancy from flights f left join t1 on f.flight_id=t1.flight_id left join t2 on f.aircraft_code=t2.aircraft_code group by 1 order by 1 ; select * from query_cost('with t1 as (select f.departure_airport,f.flight_Id,f.aircraft_code, coalesce(count(bp.seat_no),0) as cnt1 from flights f join boarding_passes bp on f.flight_id = bp.flight_id join ticket_flights tf on tf.flight_id=bp.flight_id and bp.ticket_no=tf.ticket_no where extract(''year'' from f.actual_departure) = 2017 and extract(''month'' from f.actual_departure) = 8 and fare_conditions=''Business'' group by f.departure_airport,f.flight_Id,f.aircraft_code),t2 as(select aircraft_code,coalesce(count(seat_no),0) as cnt2 from seats where fare_conditions=''Business'' group by 1) select f.departure_airport, avg(coalesce(cnt1::decimal/cnt2*100,0))as average_business_occupancy from flights f left join t1 on f.flight_id=t1.flight_id left join t2 on f.aircraft_code=t2.aircraft_code group by 1 order by 1');