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

Question

I need to order a query by the result of the sum of two sums.

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

I 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 the results

Answer 1

To do summation ordering you do not need to create Queries no. Queries like this, they work well:

select ID, SUM(QUANTIDADE_A), SUM(QUANTIDADE_B), SUM(QUANTIDADE_A + QUANTIDADE_B) AS TOTAL
from pedidos_itens
GROUP BY ID
ORDER BY TOTAL DESC

There’s a syntax in your SQL that I wouldn’t do at all.

SEC_TO_TIME( SUM( TIME_TO_SEC( a.duracao ) ) + SUM( TIME_TO_SEC( t.at_duracao ) ) )

Maybe that’s why you get weird results. I’d trade it for something more semantic:

 SEC_TO_TIME( SUM(TIME_TO_SEC(a.duracao) + TIME_TO_SEC(t.at_duracao)) )

In addition to ensuring expected results, your code is cleaner and easier to understand.

Answer 2

You can reach the desired end using a suquery and referencing the position of the column in the 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 this query each of the durations is being added up, and if you are NULL in the table, will be considered as zero, so the total sum will not be affected. After this will be ordered by the column of position 1 (Considering the ORDER BY 1).