mysql error code 1111. invalid use of group function

Question

mysql error code 1111. invalid use of group function

Navigation

#1 by (1 votes)

1

I tried to use the following query in my database:

SELECT MAX(COUNT(DISTINCT c.unique_carrier))
FROM companhia_aerea c, avioes4 a, ponte2 v
WHERE c.unique_carrier = a.UniqueCarrier AND a.TailNumber = v.TailNumb AND V.DepDelay > 0;

This query returns me the following error

mysql error code 1111. invalid use of group function.

Can anyone tell me how I can work around this problem? The structure of the tables is as follows:

CompanhiaAerea (unique_carrier PK, descricao); 
Avioes (TailNumber PK, IDModelo FK, IDTipo_licenca FK, unique_carrier FK, IDtipoAviao FK, IDTipo_Motor FK, data_registo, estado, ano); 
Voo (IDvoo PK, codigoCancelamento FK, Tail Number FK, iata_origem FK, iata_destino FK, Datavoo, Hora_partida_real, Hora_partida_prevista, Hora_chegada_real, Hora_chegada_prevista, FlightNum, AtualElapsedTime, TempoVoo, distancia, DepDelay, cancelado)

mysql query

asked by anonymous 15.08.2015 / 15:53

1 answer

Maximum size of a MySQL column Contact form HTML + PHP

score 1 · Accepted Answer

Each SELECT statement can only have one GROUP BY statement. Implicitly, your query needs two GROUP BY statements: the first to give the count of delayed flights, the second to give the maximum of the first aggregation. Hence the error message refers to an incorrect use of an aggregate function (MAX).

According to the comment, the objective is: "I want to know which airline (unique_carrier) that registers more delays of departure (DepDelay)"

Work around this problem, for example, by using a subquery.

select unique_carrier
from 
(
    select c.unique_carrier,
           count(v.IDvoo)  NumeroDeVoosAtrasados, 
           sum(v.DepDelay) TempoTotalAtraso
    from CompanhiaAerea c
    inner join Avioes a
       on a.unique_carrier = c.unique_carrier 
    inner join Voos v
       on v.TailNumber = a.TailNumber
    where v.DepDelay > 0              
    group by c.unique_carrier
) atrasos
order by NumeroDeVoosAtrasados desc, TempoTotalAtraso desc -- quando duas companhias aéreas têm o mesmo número de voos com atraso, prioritizar aquela que tem um tempo total de atraso superior
limit 1