ORACLE SQL - Sort by sum

Question

ORACLE SQL - Sort by sum

Navigation

#1 by (1 votes)

1

I need to make a report and I can not sort it as a wish, I want to select customer debit between 60 and 120 days and sort by total. The code looks 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 by ordering the larger invoices, but I need the larger sums.

sql oracle

asked by anonymous 23.02.2018 / 16:57

1 answer

Remove Class from a parent div based on the child element class Jquery - Retrieve the value of a 'td' and insert inside a Text

score 1 · Accepted Answer

I believe the SQL that you have passed is incomplete. So in the example below, I created a new column with the "value" of the customer 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 order by command 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 make SUM(vlr_fat_cli) desc that would generate the expected result and would have no problem adding new fields.

Practical example

I created the practical example here link