sql - Oracle group by clause using nested function in the select statement -

sql - Oracle group by clause using nested function in the select statement -

i have query need find available seats of flight. ie: subtract number of bookings made flight capacity of plane assigned flight. have query working, showing 'available seats' column. want show 'flight number' column "not single-group grouping function" etc errors when seek add together column in display. think need bring together tables create select statement allow me print flight_number column, i'm not sure how that. point me in right direction. lot.

select sum(p.capacity - (count(b.passenger_id))) available_seats booking b, planes p b.date_of_flight = '16-oct-2014' , depart_city = 'sydney' , arrival_city = 'perth' , flight_number in (select flight_number scheduled_flights sf sf.airplane_serial = p.airplane_serial , b.date_of_flight = sf.date_of_flight ) grouping p.capacity ;

this result:

available_seats 1 237

which correct, like: flight_number available_seats 1 tf81 237 helping :-)

maybe need this?

select flight_number, p.capacity - count(b.passenger_id) available_seats booking b, planes p, scheduled_flights sf b.date_of_flight = '16-oct-2014' , depart_city = 'sydney' , arrival_city = 'perth' , flight_number = sf.flight_number , sf.airplane_serial = p.airplane_serial , b.date_of_flight = sf.date_of_flight grouping flight_number, p.capacity;

sql oracle