problema count tablas diferentes misma query

[quimfv]

Que error te da?

Estoy escribiendo el SQL al vuelo puede haber algun error de sintaxis pero no de concepto esto funciona seguro....

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,a.numDep 
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,b.numArrivals )
) as c
GROUP BY c.iataCompanyCode;
 

hwe agregado algun alias que faltaba deberia funcionar....

Perdona quizas el error esta en usar el alias en el group by para las constates cero...

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 
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 c
GROUP BY c.iataCompanyCode;