CODEWITHSUNDEEP
sqlsql-server
Chuck
Chuck

Reputation: 1226

SQL sum for date range while showing all stock

SELECT     STOCK.NUMBER, SUM(ITEMS.QUANTO) AS USAGE, STOCK.DESC1, STOCK.UNITS, STOCK.LOW
FROM         ITEMS INNER JOIN
                      STOCK ON ITEMS.ITEM = STOCK.NUMBER
WHERE     (ITEMS.IT_SDATE BETWEEN '3/31/14' AND '4/5/14')
GROUP BY STOCK.NUMBER, STOCK.DESC1, STOCK.UNITS, STOCK.LOW

This query returns all of the items as well as the quantity shipped out this week. However it is not returning the Inventory of items not shipped this week. I need to keep the SUM of the quantity for only a specified time to see the product usage while returning all of the inventory items just showing a 0 for usage or null.

Upvotes: 0

Views: 252

Answers (1)

AHiggins
AHiggins

Reputation: 7219

If you are looking for all items from Stock, together with some values from Item, you should probably reverse the order of your JOIN condition and change it to an INNER (only items that match) to LEFT (all records from the first table along with whatever matches are found in the second), like so:

SELECT     STOCK.NUMBER, SUM(ITEMS.QUANTO) AS USAGE, STOCK.DESC1, STOCK.UNITS, STOCK.LOW
FROM
    STOCK 
     LEFT JOIN
    ITEMS ON 
        ITEMS.ITEM = STOCK.NUMBER AND 
        ITEMS.IT_SDATE BETWEEN '3/31/14' AND '4/5/14'
GROUP BY STOCK.NUMBER, STOCK.DESC1, STOCK.UNITS, STOCK.LOW

This will show you everything from the Stock table, but the SUM() of Items.Quanto will only pull data from the time period you specified. Having the date range in the JOIN condition means it will only evaluate when looking at what data to pull from the Items table, not when looking at what to pull from both tables together.

Upvotes: 1

Related Questions