Hello. I need some help. I am trying to create a query that accesses 2 tables, Sales and Requests.
SALES REQUESTS Keys Description Category NewQty InventoryKey Qty 1 Item 1 2 1 1 1 2 Item 2 3 0 2 2 3 Item 3 1 1 1 2 4 Item 4 1 1 1 1 2 2 3 1 4 1 4 1 1 1 2 1 3 1 4 1
I want to have a sum of each quantity in the Request Table per key (Hopefully that makes sense). Then, I want to join the 2 tables and have my results look something like this:
DESIRED RESULTS Description NewQty Total Requests Item 1 1 5 Item 2 0 5 Item 3 1 2 Item 4 1 3
I tried this with COUNT and the results were great but I need to SUM the qty from the Requests table.
Here's my SQL using COUNT:
SELECT Sales.Description, Sales.NewQty, COUNT(Requests.InventoryKey) AS [Total Requests]
FROM Sales LEFT JOIN Requests ON Sales.Keys = Requests.InventoryKey
WHERE Sales.Category <> 7 AND Sales.Category <> 38
GROUP BY Sales.Description, Sales.NewQty, Requests.InventoryKey, Sales.Category
ORDER BY Sales.Category, Sales.Description;
I've tried replacing COUNT with SUM, but that just sums the whole column. It's probably something easy I'm missing, but any help would be appreciated.
Thanks in advance.