MYSQL - add column and calculate days late of a date and sort by the longest delay

Question

Hello,

Suppose I have a table tb_fatura with the following fields and values:

id | transacao_id | valor    | vencimento
1  | 1            | 1109.97  | 2020-02-19
2  | 1            | 10,      | 2019-05-01

I want somar the column valor, calcular the dias em atraso do vencimento and order by the greatest delay.

With the query below I can even get some kind of result, but still not what I need:

SELECT *, SUM(valor) AS total, DATEDIFF(CURDATE(), vencimento) as atraso
FROM tb_fatura
WHERE transacao_id = 1
GROUP BY transacao_id

This brings me the following result:

id | transacao_id | valor   | vencimento | total   | atraso
1  | 1            | 1109.97 | 2020-02-19 | 1119.97 | 91

This does not suit me because I need to order for the biggest delay and even adding order by, I couldn’t! Doing this is possible ?

Answer 1

Responding based on the comment, just use a MAX where calculates the days difference (delay):

SELECT SUM(valor) AS total, MAX(DATEDIFF(CURDATE(), vencimento)) as atraso
  FROM tb_fatura
 WHERE transacao_id = 1
 GROUP BY transacao_id;

You can see it working on http://sqlfiddle.com