CODEWITHSUNDEEP
sqlpostgresqlsorting
Marco Antonio Gomez Martinez
Marco Antonio Gomez Martinez

Reputation: 45

Sorting values obtained from a query in postgresql

select SUM (Account_Invoice.amount_untaxed),
       right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC 
from Account_Invoice inner join Res_Partner on Account_Invoice.partner_id = Res_Partner.id 
     inner join Account_Invoice_Tax on Account_Invoice.id = Account_Invoice_Tax.invoice_id 
where account_invoice.journal_id=2
      and account_invoice.date_invoice >= '2013-01-01' 
      and account_invoice.date_invoice <= '2013-02-01'
      and account_invoice.reconciled is TRUE 
      and account_invoice_tax.account_id = 3237 and account_invoice.amount_tax >= 0
      or account_invoice_tax.account_id = 3236 or account_invoice_tax.account_id = 3238

I'm hoping to sort the untaxed amounts from 3236 then the ones for 3237 and finally the ones with 3238. I understand I have to use "group by" but I don't understand exactly how. Any pointers?

Upvotes: 1

Views: 67

Answers (2)

Marco Antonio Gomez Martinez
Marco Antonio Gomez Martinez

Reputation: 45

This is what worked for me, it sorts the values from 3236, then 3237, then 3238.

select SUM(Account_Invoice.amount_untaxed) as monto, 
right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC
from Account_Invoice 
inner join Res_Partner on Account_Invoice.partner_id = Res_Partner.id 
inner join Account_Invoice_Tax on Account_Invoice.id = Account_Invoice_Tax.invoice_id 
where account_invoice.journal_id=2 
and account_invoice.date_invoice >= '2013-01-01' 
and account_invoice.date_invoice <= '2013-02-01' 
and account_invoice.reconciled is TRUE 
and account_invoice_tax.account_id in (3237,3236,3238)
and account_invoice.amount_tax >= 0
GROUP BY vat,account_invoice_tax.account_id
ORDER BY account_invoice_tax.account_id;

Thanks for the help guys.

Upvotes: 1

Edper
Edper

Reputation: 9322

Try ORDER BY SUM()

select account_invoice_tax.account_id, SUM (Account_Invoice.amount_untaxed),
   right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC 
from Account_Invoice inner join Res_Partner on Account_Invoice.partner_id = Res_Partner.id 
 inner join Account_Invoice_Tax on Account_Invoice.id = Account_Invoice_Tax.invoice_id 
where account_invoice.journal_id=2
  and account_invoice.date_invoice >= '2013-01-01' 
  and account_invoice.date_invoice <= '2013-02-01'
  and account_invoice.reconciled is TRUE 
  and account_invoice_tax.account_id IN(3236,32387,323)  
  AND account_invoice.amount_tax >= 0
GROUP BY account_invoice_tax.account_id
ORDER BY account_invoice_tax.account_id, SUM (Account_Invoice.amount_untaxed)

Upvotes: 0

Related Questions