Dolly Dimple Dolly Dimple - 2 months ago 5
SQL Question

SQL Group View Query and join with Other table

sqlstring = "Select PurchasesBody.ItemId, Items.Iteminfo, Sum(TTLQtyLose) As PurQty, Avg(PerUnitCost) AS AvgRate, Sum(TTLValue) AS NetValue From PurchasesBody left Join Items On PurchasesBody.ItemId=Items.ItemId Group By PurchasesBody.ItemId"


When i Try to get Items.Iteminfo then error occurs. Can any one help where i am wrong..

Answer

You have to include columns in the SELECT list to the GROUP BY clause.

    Select PurchasesBody.ItemId, 
       Items.Iteminfo, Sum(TTLQtyLose) As PurQty, 
       Avg(PerUnitCost) AS AvgRate, Sum(TTLValue) AS NetValue 
     From PurchasesBody 
       left Join Items On PurchasesBody.ItemId=Items.ItemId 
     Group By PurchasesBody.ItemId,Items.Iteminfo