problema count tablas diferentes misma query

[quimfv]

Siempre los alias....

Código MySQL:

SELECT c.iataCompanyCode as company, 
sum(c.numArrivals) as Arrivals, 
sum(c.numDep) as Departures 
FROM ((SELECT DISTINCT a.iataCompanyCode as company, 
                                       count(a.idArrivalFlight) as numArrivals, 
                                       0 as numDep 
                       FROM `aodb`.`arrivalFlight` a 
                       WHERE a.iataCompanyCode IN ('SPP','IB') 
                       AND a.scheduledTime BETWEEN '2011-01-01 00:00:00' 
                                  AND '2011-09-01 00:00:00' 
                       GROUP BY a.iataCompanyCode,0) as Sbc1  
UNION all 
           (SELECT DISTINCT b.iataCompanyCode as company, 
                                       0 as numArrivals, 
                                       count(b.idDepartureFlight) as numDep 
             FROM `aodb`.`departureflight` b 
             WHERE b.iataCompanyCode IN ('SPP','IB') 
             AND b.scheduledTime BETWEEN '2011-01-01 00:00:00' 
                  AND '2011-09-01 00:00:00' 
             GROUP BY b.iataCompanyCode,0) as sbc2
) as c 
GROUP BY c.iataCompanyCode;
 

Yo diria que el union no necesita alias pero por intentarlo no pasa nada....

He puesto alias a las subquery del UNION (sbc1,sbc2), tambien he agregado el alias c a los SUM ...

En tu codigo sobra un parentesis dse cierre )) as c

Ya te digo que estoy escribiendo al vuelo, se que debe funcionar, pero no tengo tu estructura para probarlo... pero como te dice es un problema de alias (Every derived table must have its own alias Toda tabla derivada debe tener su propio alias)

...
GROUP BY a.iataCompanyCode,0
...
GROUP BY b.iataCompanyCode,0