lundi 30 novembre 2015

sql not calculating sum correctly

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