You need to GROUP BY those fields NOT in the aggregate function. Like:
SELECT f.id,f.name,sum(p.price) FROM fruit f left join purchase p on f.id = p.fruit where p.price is not null group by f.id,f.name; 1, 'Apples', 2 2, 'Grapes', 6.5 4, 'Kiwis', 4 On 2015-10-23 04:15, Don Wieland wrote:
Hi gang, I have a query: SELECT p.pk_ProductID, p.Description, i.Quantity FROM invoice_invoicelines_Product p JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3) WHERE p.pk_ProductID IN (1,2,3); It produces a list like the following: 1,Banana,3 2,Orange,1 2,Orange,4 3,Melon,3 3,Melon,3 I want to SUM the i.Quantity per ProductID, but I am unable to get the scope/syntax correct. I was expecting the following would work: SELECT p.pk_ProductID, p.Description, SUM(i.Quantity) FROM invoice_invoicelines_Product p JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3) WHERE p.pk_ProductID IN (1,2,3) GROUP BY i.fk_ProductID; but it is not working. Little help please. Thanks! Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band
-- Mogens +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
