Ver Mensaje Individual
  #7 (permalink)  
Antiguo 06/09/2011, 04:02
Pelirr
 
Fecha de Ingreso: diciembre-2008
Mensajes: 233
Antigüedad: 15 años, 4 meses
Puntos: 1
Respuesta: problema count tablas diferentes misma query

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(' at line 1

Eso era por que el FROM estaba pegado al (, he separado algunas cosas así y la sentencia queda:

SELECT c.iataCompanyCode as company, sum(numArrivals) as Arrivals, sum(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,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,numArrivals )) as c GROUP BY c.iataCompanyCode;

y el error es:

Error Code: 1248
Every derived table must have its own alias

No entiendo porqué a uno nos funciona y al otro no, siendo la misma consulta.