CODEWITHSUNDEEP
mysqlsqlaggregate-functions
jogesh_pi
jogesh_pi

Reputation: 9782

Getting SUM() from multiple tables with conditions

I am trying to get the due_amount from two tables that is invoices and offline_invoice.
The condition is; status not like 'paid%'. I am working with this query

select 
    (sum(i.total_amount) + sum(oi.invoice_amount)) - (sum(i.paid_amount) + sum(oi.paid_amount)) due_amount
from
    {CI}invoices i
        left join
    {CI}offline_invoice oi ON oi.customer_id = i.customer_id
where
    i.customer_id = ?
        and i.status not like 'paid%'
group by i.customer_id

But i don't know how do i use condition on joined table({CI}offline_invoice)? I have to use the same condition(status not like 'paid%') on it.

Upvotes: 0

Views: 65

Answers (1)

DRapp
DRapp

Reputation: 48139

Just add the and to the ON clause too

   left join
    {CI}offline_invoice oi ON oi.customer_id = i.customer_id
       AND oi.status not like 'paid%'
where
    i.customer_id = ?
        and i.status not like 'paid%'

However, I'm not sure how this is going to work for you without possible Cartesian impact. Say you have 10 current invoice and 10 and 6 offline invoices. I would do two separate pre-aggregates joined by their customer ID... unless the off-line invoice has the same invoice ID as current (such as archive purposes)

select 
      CurInvoices.Customer_ID,
      CurInvoices.InvBalance + COALESCE( OIInvoices.OIBalance, 0 ) as AllBalanceDue
   from
      ( select i.customer_id,
               sum( i.total_amount - i.paid_amount ) as invBalance
           from
              {CI}invoices i
           where
                  i.customer_id = ?
              and i.status not like 'paid%'
           group by
              i.customer_ID ) as CurInvoices
      LEFT JOIN 
      ( select oi.customer_id,
               sum( oi.total_amount - oi.paid_amount ) as OIBalance
           from
              {CI}offline_invoice oi
           where
                  oi.customer_id = ?
              and oi.status not like 'paid%' 
           group by
              i.customer_ID ) as OIInvoces
         on CurInvoices.Customer_ID = OIInvoices.customer_ID

Upvotes: 1

Related Questions