Web lists-archives.com

Re: Query Summary Help...




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 <donw@xxxxxxxxxxxxx> 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
> donw@xxxxxxxxxxxxx
> http://www.pointmade.net
> https://www.facebook.com/pointmade.band
>
>
>
>
>