mysql error code 1111. invalid use of group Function

Asked

Viewed 3,043 times

1

I tried using 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 get 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

  • What is the structure of the 3 tables?

  • i would try this: SELECT MAX(SELECT COUNT(DISTINCT c.unique_carrier) FROM compaia_aerea c, avioes4 a, ponte2 v WHERE c.unique_carrier = a.Uniquecarrier AND a.Tailnumber = v.Tailnumb AND V.Depdelay > 0);

  • 1

    So that the MAX, in this case?

  • The database is about flights. I want to know which airline (unique_carrier) has the most delays in departure (Depdelay). Hence the "MAX(COUNT".

  • A estrutura das tabelas é a seguinte: 
 CompanhiaAerea(unique_carrier PK, descricao); 

Aviaoes4 (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)

  • @Pedrocunha, check it out: you are doing MAX on top of COUNT(DISTINCT c.unique_carrier). This adds nothing, because COUNT will return only one number and you are giving MAX on it, IE, will return itself. By your comment, the SQL you need is another. Depdelay is INT?

  • What is the complete structure of the 3 tables? Edit the question and post the code.

  • @Cantoni Depdelay has numerical values. In the case where the flight instead of delay is anticipated for example 5 minutes what appears in the data is "-5". I do not have as INT because I have 3 million lines with the NA value, for cases where there is no information of the duration of the delay. That’s why I can sweep...

Show 3 more comments

1 answer

1


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

According to the comment, the objective is: "I want to know which airline (unique_carrier) has the most delays in departure (Depdelay)"

The way around this problem is, 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

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.