ORACLE SQL - Sort by sum

Question

I need to make a report and I am not being able to sort it as I wish, I want to select customer debt between 60 and 120 days and sort by total. The code is like this:

SELECT 
  SUM(cod_fat_cli) "Total",
  nom_cad_cli "Nome",
  end_cad_cli "Endereço"
FROM 
  cadastro_cliente
WHERE 
  --Seleciona as faturas em aberto
  cod_fat_cli = 'AB' 
  --Seleciona as faturas vencidas entre 60 e 120 dias
  AND venc_fat_cli BETWEEN trunc(sysdate -120) AND trunc(sysdate - 60) 

Doubt

If I order by cod_fat_cli returns ordering the largest invoices, but I need the largest sums.

Answer 1

I believe the SQL you passed is incomplete. So in the example below, I created a new column with the "value" of the customer’s invoice, with the name vlr_fat_cli.

SQL

SELECT 
  SUM(vlr_fat_cli) "Total",
  nom_cad_cli "Nome"  
FROM 
  cadastro_cliente
WHERE 
  --Seleciona as faturas em aberto
  cod_fat_cli = 'AB' 
  --Seleciona as faturas vencidas entre 60 e 120 dias
  AND venc_fat_cli BETWEEN trunc(sysdate -120) AND trunc(sysdate - 60)
group by
  nom_cad_cli
order by
  1 desc

Explanation

After the command order by for sorting, you can use the column number of the field you want to sort, as I did above. But be careful, when editing SQL and putting more fields you have to remember to adjust the order by.

You could also do in place of 1 desc do SUM(vlr_fat_cli) desc that would generate the expected result and would have no problems adding new fields.

Practical example

I created the practical example here http://sqlfiddle.com/#! 4/4359c/9