I've got the following sql query, it seems to be returning data, but the sum of the invoice_total (4th column) is not calculating properly. At the moment although I have about 50 records in purchase files with the duplicate supplierID, I currently only have 4 individual supplier records filled with the invoice_total. The returned result is not what I have put in.
SELECT p.orderID, s.supplier, SUM(IFNULL(pl.line_price,0)) AS total_order, SUM(IFNULL(p.invoice_total,0)) AS invoice_total
FROM purchase p
LEFT JOIN purchase_line pl
ON p.orderID = pl.orderID
LEFT JOIN supplier s
ON p.supplierID = s.supplierID
WHERE (p.date BETWEEN '2015-01-01' AND '2015-12-30')
GROUP BY p.supplierID
ORDER BY p.supplierID ASC
Aucun commentaire:
Enregistrer un commentaire