Ver Mensaje Individual
  #6 (permalink)  
Antiguo 06/09/2011, 03:56
quimfv
Colaborador
 
Fecha de Ingreso: marzo-2008
Ubicación: Sabadell
Mensajes: 4.897
Antigüedad: 16 años, 4 meses
Puntos: 574
Respuesta: problema count tablas diferentes misma query

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:
Ver original
  1. SELECT c.iataCompanyCode as company,
  2.           Sum(c.numArrivals) as Arrivals,
  3.           Sum(c.numDep) as Departures
  4.           a.iataCompanyCode as company,
  5.           count(a.idArrivalFlight) as numArrivals,
  6.           0  as numDep
  7. FROM `aodb`.`arrivalFlight` a
  8. WHERE a.iataCompanyCode IN ('SPP','IB')
  9. AND a.scheduledTime BETWEEN '2011-01-01 00:00:00' AND '2011-09-01 00:00:00'
  10. GROUP BY a.iataCompanyCode,a.numDep
  11. SELECT DISTINCT b.iataCompanyCode as company,
  12.           0 as numArrivals,
  13.           count(b.idDepartureFlight) as numDep
  14.           FROM `aodb`.`departureflight` b
  15.      WHERE b.iataCompanyCode IN ('SPP','IB')
  16.      AND b.scheduledTime BETWEEN '2011-01-01 00:00:00' AND '2011-09-01 00:00:00'
  17. GROUP BY b.iataCompanyCode,b.numArrivals )
  18. ) as c
  19. 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:
Ver original
  1. SELECT c.iataCompanyCode as company,
  2.           Sum(c.numArrivals) as Arrivals,
  3.           Sum(c.numDep) as Departures
  4.           a.iataCompanyCode as company,
  5.           count(a.idArrivalFlight) as numArrivals,
  6.           0  as numDep
  7. FROM `aodb`.`arrivalFlight` a
  8. WHERE a.iataCompanyCode IN ('SPP','IB')
  9. AND a.scheduledTime BETWEEN '2011-01-01 00:00:00' AND '2011-09-01 00:00:00'
  10. GROUP BY a.iataCompanyCode,0
  11. SELECT DISTINCT b.iataCompanyCode as company,
  12.           0 as numArrivals,
  13.           count(b.idDepartureFlight) as numDep
  14.           FROM `aodb`.`departureflight` b
  15.      WHERE b.iataCompanyCode IN ('SPP','IB')
  16.      AND b.scheduledTime BETWEEN '2011-01-01 00:00:00' AND '2011-09-01 00:00:00'
  17. GROUP BY b.iataCompanyCode,0 )
  18. ) as c
  19. GROUP BY c.iataCompanyCode;
__________________
Quim
--------------------------------------------------
Ayudar a ayudar es una buena práctica!!! Y da buenos resultados.

Última edición por quimfv; 06/09/2011 a las 04:02