I'm not at a terminal but have you tried grouping by p.pk_ProductID instead of i.fk...? It is the actual value you are selecting as well as being on the primary table in the query.
On Thu, Oct 22, 2015, 5:18 PM Don Wieland <d...@pointmade.net> 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 > > > > >
