SQLize | PHPize | SQLtest

with t1 as (select distinct bp.flight_id,coalesce(count(case when fare_conditions='Business' then seat_no else null end),0) as cnt1, coalesce(count(case when fare_conditions='economy' then seat_no end),0) as cnt2 from ticket_flights tf join boarding_passes bp on tf.flight_id=bp.flight_id and tf.ticket_no=bp.ticket_no group by 1),t2 as(select aircraft_code,coalesce(count(case when fare_conditions='Business' then seat_no else null end),0) as cnt1,coalesce(count(case when fare_conditions='economy' then seat_no end),0) as cnt2 from seats group by 1) select f.departure_airport, round(avg(coalesce(t1.cnt1::numeric * 100/t2.cnt1,0)),2) 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 where extract('year' from f.actual_departure) = 2017 and extract('month' from f.actual_departure) = 8 group by 1 order by 1