I know this is wrong, how do I make it right?
I need to find out, what sold, how many, at what cost, at what retail price, from a specific vendor during a specific time period.
The only unique thing should be ii.itemnum
This query works, but I know that I made a mess of it with the group by clause.
SELECT ii.itemNum, SUM(ii.Quantity) as quantity, SUM(ii.CostPer) as cost, SUM(ii.PricePer) as price
FROM invoice_itemized ii
LEFT OUTER JOIN Inventory i
ON ii.ItemNum = i.ItemNum
LEFT OUTER JOIN vendors v
ON i.vendor_number = v.vendor_number
LEFT OUTER JOIN Invoice_Totals it
ON ii.invoice_number = it.invoice_number
WHERE it.DateTime BETWEEN '09/01/2011' AND '09/30/2011'
AND v.Vendor_Number = '8185021313'
GROUP BY ii.ItemNum, ii.quantity, ii.CostPer, ii.PricePer