1
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?

2 Answers 2

1

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.

0

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)
1
  • Hello, it gave me an error saying: the column res_partner.vat must in group by clause or be used in the sum function from line 3: LINE 3: right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC Commented Nov 21, 2013 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.