Sort by the result of the sum of two SUM - MYSQL

Question

Sort by the result of the sum of two SUM - MYSQL

Navigation

#1 by (0 votes)

2

Hello, I need to sort a query by the result of the sum of two sums.

ORDER BY SUM (SUM () + SUM ())

I've tried the following:

SELECT 
   SUM(campo1 + campo2)   AS soma1
   , SUM(campo3 + campo4) AS soma2
   , SUM(soma1 + soma2)   AS ordenacao
WHERE 
   condicoes_where 
ORDER BY 
   ordenacao 
DESC

The query is this.

SELECT 

SEC_TO_TIME( SUM( TIME_TO_SEC( x.tempo_total_atendimento ) ) + SUM( TIME_TO_SEC( x.tempo_chat ) ) ) AS ordenacao

FROM (
        SELECT

            case when SEC_TO_TIME( SUM( TIME_TO_SEC( a.duracao ) ) + SUM( TIME_TO_SEC( t.at_duracao ) ) ) is null then 
                SEC_TO_TIME( SUM( TIME_TO_SEC( a.duracao ) ) )
            else
                SEC_TO_TIME( SUM( TIME_TO_SEC( a.duracao ) ) + SUM( TIME_TO_SEC( t.at_duracao ) ) )
            end AS tempo_total_atendimento,

            SEC_TO_TIME( SUM( TIME_TO_SEC( TIMEDIFF(c.con_data_fim, DATE_FORMAT(c.con_data, '%H:%i:%s')) ) ) ) AS tempo_chat

            FROM atendimentos AS a 
            LEFT OUTER JOIN atendimentos_texto AS t ON t.atendimento = a.id
            LEFT OUTER JOIN chat_conversa AS c ON c.con_file = a.chat

            WHERE a.data_inicio BETWEEN '2017-01-01 00:00:00' AND '2017-09-26 23:59:59'

            GROUP BY a.cliente
        ) x

Prints of results

If you could help me, I would be very grateful.

Thank you

mysql

asked by anonymous 26.09.2017 / 13:51

1 answer

When registering my class, stick with the OnAppearing method Print with Java with printer configured by IP

score 0 · Answer 1

You can reach the desired end by using a suquery and referencing the column position in ORDER BY :

SELECT x.atendimento_duracao + x.texto_duracao + chat_duracao AS total_duracao
  FROM (SELECT SUM(COALESCE(TIME_TO_SEC(a.duracao), 0)) atendimento_duracao,
               SUM(COALESCE(TIME_TO_SEC(at.at_duracao), 0)) texto_duracao,
               SUM(COALESCE(TIME_TO_SEC(TIMEDIFF(c.con_data_fim, DATE_FORMAT(c.con_data, '%H:%i:%s'))), 0)) chat_duracao
          FROM atendimentos a
               LEFT JOIN atendimentos_texto at ON at.atendimento = a.id
               LEFT JOIN chat_conversa cc ON cc.con_file = a.chat
         WHERE a.data_inicio BETWEEN '2017-01-01 00:00:00' AND '2017-09-26 23:59:59'
         GROUP BY a.cliente) x
 ORDER BY 1

In%% of each duration is being added, and if it is query in the table, it will be considered zero, so the total sum will not be affected. After this it will be sorted by the column of position 1 (Considering NULL ).