mercredi 22 avril 2015

sql sum from two tables

I've got two separate tables in sqlite called invoices and purchases and I am using the query below to retrieve the sum of all the invoices and purchases that related to project 7. The thing is the invoices have three records and the value returned in sql is correct, however the purchase equivalent is wrong as there is only one record, but the returned value is multiplied by three.

SELECT sum(invoice.invoice_net) As Sales, sum(purchase.total_order) As Purchases
FROM invoice
LEFT JOIN purchase
ON purchase.projectID=invoice.projectID
WHERE invoice.projectID=7

How can I join these two statements so I get the data returned correctly. I know individually they work fine. I've tried Union, but that puts the data into one column.

SELECT sum(invoice.invoice_net) As Sales
FROM invoice
WHERE projectID=7

SELECT sum(purchase.order_total) As Purchases
FROM purchase
WHERE projectID=7

Aucun commentaire:

Enregistrer un commentaire