CODEWITHSUNDEEP
mysqlselectjoinsumunion
user3684020
user3684020

Reputation: 103

Combine Sum with select join and union

I need to join 2 query like this

SELECT * from contracts join customers on customer.id=contracts.customer

and (12234 is an explample customer)

SELECT *,sum(cast(amount as UNSIGNED)) as total FROM(    
SELECT * from invoices
where customer='123456'
UNION
SELECT * from paypal_invoices
where customer='123456') t1

In the first query I simply extract a normal customers list based on the actives contracts we have.

In the second i extract the sum of any invoice for a specific customers

So i basically need to extract all customers like the first query but i need also a column with the sum of all invoice for this specific customer. Thank you.

Upvotes: 0

Views: 399

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method is correlated subqueries or left joins:

select co.*, cu.*, i.amount, pi.amount as paypal_amount,
       ( coalesce(i.amount, 0) + coalesce(pi.amount, 0) ) as total       
from contracts co join
     customers cu
     on cu.id = co.customer left join
     (select customer, sum(amount) as amount
      from invoices
      group by customer
     ) i
     on i.customer = cu.id left join
     (select customer, sum(amount) as amount
      from paypal_invoices
      group by customer
     ) pi
     on pi.customer = cu.id;

Two key ideas here. First, be sure that you aggregate before doing the joins. This prevents joins from multiplying the number of rows and generating incorrect results. Similarly, this uses LEFT JOIN to ensure that all customers are included, even those with no invoices.

Second idea: Don't use UNION. Your query, in fact, is possibly incorrect. UNION removes duplicates -- both within a table and between tables. It is much safer to use UNION ALL. Or, in this case, it is not needed at all.

Upvotes: 2

Related Questions