problema count tablas diferentes misma query

[Pelirr]

Hola, intentaré explicarme bien. Tengo dos tablas que no están relacionadas (no para lo que quiero hacer). Quiero hallar el número de registros de cada tabla dentro de un intervalo de fechas concreto, y guardar cada cuenta en un nombre de campo diferente. Para una tabla sería en numArrivals y para la otra en numDep. Además quiero agrupar el número de registros que tengo en cada tabla por el campo iataCompanyCode. Intento utilizar "union all" y "group by", pero haciéndolo así me lo guarda todo en el campo numArrivals. No consigo que salga cada cosa en su campo, según la tabla de que se trate, y necesito hacer todas las consultas en una sola query, ya que estoy tratando de obtener un informe con ireport de jasperreport.

Esta es la consulta:

select distinct a.iataCompanyCode as company, count(a.idArrivalFlight) as numArrivals 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 union all (select distinct b.iataCompanyCode as company, 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 );
commit;

y lo que obtengo es:

company ............. numArrivals
IB .......................... 23
SPP .......................... 1
IB .......................... 12
SPP .......................... 1

como véis no me aparece numDep por ninguna parte.

¿Alguien tiene alguna idea de como lo puedo agrupar o cómo lo puedo hacer, para obtener lo que quiero de ambas tablas? Ya digo que son cuentas por separado, lo que ocurre que tengo que utilizarlas en la misma query para el informe.

Muchas gracias, un saludo

[quimfv]

Código MySQL:

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;
 

Preguntate si esos DISTINCT son necesarios... puesto que tienen su coste...

[Pelirr]

Hola quimfv, pero eso ya lo había probado, el problema es que entonces lo que obtengo es algo parecido a ésto en el informe:

company ............. numArrivals..................numDep
IB .......................... 23 ............................0
SPP .......................... 1 .............................0
IB ............................. 0 ............................12
SPP .......................... 0 ............................ 1

y yo lo que necesito conseguir es lo siguiente:

company ............. numArrivals..................numDep
IB .......................... 23 ............................12
SPP .......................... 1 .............................1

He ahí el problema.

Reconozco que mi conocimiento de msql es limitado, de modo que no he conseguido hacerlo de ninguna manera. Pero os voy a dejar cómo he conseguido al final mostrar lo que necesito:

En la consulta del jasperReport, he hecho lo siguiente:

select distinct e.iataCompanyCode as company from `aodb`.`arrivalFlight` e where e.iataCompanyCode in ($P!{listadoAerolineas}) union (select distinct f.iataCompanyCode as company from `aodb`.`departureflight` f where f.iataCompanyCode in ($P!{listadoAerolineas}));

que parece redundante, porque el parametro listadoAerolineas contiene los mismos valores que obtengo con la query, es decir, IB y SPP en el caso anterior. Pero de éste modo, en el reporte, obtengo como variable company esos valores, que utilizo en un subreporte que ya me trae los datos que necesito, y cuya query es la siguiente:

select (select count(*) from `aodb`.`arrivalflight` a where a.iataCompanyCode = $P{company} and a.scheduledTime >= $P{fechaInformeDesde} and a.scheduledTime < $P{fechaInformeHasta}) as numArr, (select count(*) from `aodb`.`departureflight` b where b.iataCompanyCode = $P{company} and b.scheduledTime >= $P{fechaInformeDesde} and b.scheduledTime < $P{fechaInformeHasta}) as numDep, ((select count(*) from `aodb`.`arrivalflight` a where a.iataCompanyCode = $P{company} and a.scheduledTime >= $P{fechaInformeDesde} and a.scheduledTime < $P{fechaInformeHasta}) + (select count(*) from `aodb`.`departureflight` b where b.iataCompanyCode = $P{company} and b.scheduledTime >= $P{fechaInformeDesde} and b.scheduledTime < $P{fechaInformeHasta})) as totalMov;

De este modo, puedo mostrar :

compañia.....................numArr............... .....numDep .......................totalMov

para cada compañía. No he conseguido hacerlo todo en una sola query, porque no podía hallar el total (el count(*)) para cada compañia por separado, todo en la misma query.

Por cierto que los distintc los utilizo porque si no me trae valores repetidos y eso no me vale.

Muchas gracias por la respuesta, espero que a alguien le sirva la mía.

Un saludo

[quimfv]

No, fijate en esta parte

Código MySQL:

SELECT c.iataCompanyCode as company, 
          Sum(numArrivals) as Arrivals, 
          Sum(numDep) as Departures
FROM
(
... aqui el union all....
) as c
GROUP BY c.iataCompanyCode;
 

El UNION ALL te da esto

company ............. numArrivals..................numDep
IB .......................... 23 ............................0
SPP .......................... 1 .............................0
IB ............................. 0 ............................12
SPP .......................... 0 ............................ 1

si ahora le aplicas (en la misma query) otro group by con SUM consigues


company ............. numArrivals..................numDep
IB .......................... 23 ............................12
SPP .......................... 1 .............................1

ya que 0+X=X cero mas lo que sea es lo que sea...

Por cierto que los distintc los utilizo porque si no me trae valores repetidos y eso no me vale.

Lo que debes preguntarte es si hay valores repetidos, no conozco tus datos pero no tiene mucho sentido que un vuelo este dos veces en la bbdd, incluir el DISTINCT tiene coste aún que no haya repetidos... si los hay yo repasaria el resto de programacion para que directamente no entren al sistema....(DISTINCT solo elimina aquellos registros donde TODOS los campos son iguales)

[Pelirr]

Perdona quimfv, estoy intentando probar tu sentencia sql en el mysql workbench y me da error, ¿estás seguro que es la que querías escribir? No puedo reproducir los resultados.

Un saludo

[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;
 

[Pelirr]

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.

[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

[Pelirr]

Pues me sigue dando error en la linea 19:

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 'as' at line 20

parece como si faltara o sobrara algún paréntesis, pero no veo dónde.

Un saludo

[Pelirr]

perdón, la linea 20

[quimfv]

Ya tengo el error, es el alias iataCompanyCode as company en el union ...

Código MySQL:

SELECT c.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.company;
 

O lo quitas o como te he puesto lo usas en el ultimo group by

SELECT c.company,
sum(c.numArrivals) as Arrivals,
sum(c.numDep) as Departures
FROM (

) as c
GROUP BY c.company;

Quita los alias en el union no son necesarios...

[Pelirr]

ok, lo he quitado, y además he cambiado el 0 en las lineas 11 y 20 por numDep y numArrivals respectivamente, y ya funciona.

Muchas gracias, menudo lío de query.

un saludo