SQL + Medium Query

Question

Gentlemen, I have an appointment that makes the calculation by line.

The result is in the field calculo_diff.

Calculation is the difference DataHoraTerno,as shown below:

Now I need to make an average of calulo_diff, as described.

That is, it would be an average of:

Operando H. Norma

Waiting for Carreta

Someone can help me?

SELECT      Descricao
        ,   DataHoraTerno
        ,   @linha          := TIME_FORMAT(TIMEDIFF(@DataHoraTerno, DataHoraTerno), '%H%i') AS calculo_diff
        ,   @DataHoraTerno  := DataHoraTerno                                                AS valor_aux
        ,   @voltas         := @voltas + 1                                                  AS voltas
FROM        EmbarqueNavio
        ,   (
                SELECT  @DataHoraTerno  := 0
                    ,   @voltas         := 0
            ) AS vars
WHERE       NavioViagem = 3
        AND Terno       = 1
ORDER BY    DataHoraTerno DESC

Answer 1

I’m guessing that’s what you want:

+---------------------+-----------------------+
| Descrição           | Média calculo_diff    |
+---------------------+-----------------------+
| Operando H. Norma   |   15                  |
+---------------------+-----------------------+
| Aguardando Carreta  |   20                  |
+---------------------+-----------------------+

If the field calculo_diff were it not a calculated field, it would be something like:

SELECT Descricao, AVERAGE(calculo_diff) as media_calculo_diff FROM tabelanome 
GROUP BY Descricao;

In your case tabelanome could be the result of the query you already make.

Answer 2

Try it this way:

SELECT      X.Descricao
        ,   AVG(X.calculo_diff) AS Media
FROM        (
                SELECT      Descricao
                        ,   DataHoraTerno
                        ,   @linha          := TIME_FORMAT(TIMEDIFF(@DataHoraTerno, DataHoraTerno), '%H%i') AS calculo_diff
                        ,   @DataHoraTerno  := DataHoraTerno                                                AS valor_aux
                        ,   @voltas         := @voltas + 1                                                  AS voltas
                FROM        EmbarqueNavio
                        ,   (
                                SELECT  @DataHoraTerno  := 0
                                    ,   @voltas         := 0
                            ) AS vars
                WHERE       NavioViagem = 3
                        AND Terno       = 1
                ORDER BY    DataHoraTerno DESC
            ) X
GROUP BY    X.Descricao