SQLize | PHPize | SQLtest

select f.departure_airport,ROUND(AVG(bp_business::numeric/ seats_business * 100), 2) AS average_business_occupancy, ROUND(AVG(bp_economy::numeric/ seats_economy * 100 ), 2) AS average_economy_occupancy FROM flights f JOIN ( SELECT bp.flight_id, SUM(CASE WHEN fare_conditions= 'business' THEN 1 ELSE 0 END) AS bp_business, SUM(CASE WHEN fare_conditions ='economy' THEN 1 ELSE 0 END) AS bp_economy FROM boarding_passes bp join ticket_flights tf on bp.ticket_no=tf.ticket_no and bp.flight_id=tf.flight_id GROUP BY bp.flight_id ) bp ON f.flight_id = bp.flight_id JOIN(SELECT s. aircraft_code, SUM(CASE WHEN s.fare_conditions= 'business' THEN 1 ELSE null END) As seats_business, SUM(CASE WHEN s.fare_conditions= 'economy' then 1 else null end) as seats_economy from seats s group by 1) s ON f.aircraft_code = s.aircraft_code WHERE f.actual_departure>= '2017-08-01' AND f.actual_departure < '2017-09-01' GROUP BY f.departure_airport ORDER BY f.departure_airport