SQL 组和按月求和 - 默认为零 [英] SQL Group and Sum By Month - Default to Zero

问题描述

我目前正在按月对库存使用情况进行分组和汇总:

I am currently grouping and summing inventory usage by month:

SELECT      Inventory.itemid AS ItemID,
            SUM(Inventory.Totalunits) AS Individual_MonthQty,
            MONTH(Inventory.dadded) AS Individual_MonthAsNumber,
            DATENAME(MONTH, Inventory.dadded) AS Individual_MonthAsString
FROM        Inventory
WHERE       Inventory.invtype = 'Shipment'
AND         Inventory.dadded >= @StartRange
AND         Inventory.dadded <= @EndRange
GROUP BY    Inventory.ItemID, 
            MONTH(Inventory.dadded), 
            DATENAME(MONTH, Inventory.dadded)

这给了我期望的结果:

ItemID  Kit_MonthQty    Kit_MonthAsNumber   Kit_MonthAsString
13188   234             8                   August
13188   45              9                   September
13188   61              10                  October
13188   20              12                  December

问题

我必须做什么才能在没有数据存在的月份返回零，如下所示:

What must I do to return zero for months where no data exsits, like this:

ItemID  Kit_MonthQty    Kit_MonthAsNumber   Kit_MonthAsString
13188   0               1                   January
13188   0               2                   February
13188   0               3                   March
13188   0               4                   April
13188   0               5                   May
13188   0               6                   June
13188   0               7                   July
13188   234             8                   August
13188   45              9                   September
13188   61              10                  October
13188   0               11                  November
13188   20              12                  December

推荐答案

过去，我通过创建一个临时表来解决这样的问题:

In the past, I've solved a problem like this by creating a temporary table which will hold all dates needed:

    CREATE TABLE #AllDates (ThisDate datetime null)
    SET @CurrentDate = @StartRange

    -- insert all dates into temp table
    WHILE @CurrentDate <=  @EndRange
        BEGIN
            INSERT INTO #AllDates values(@CurrentDate)
            SET @CurrentDate = dateadd(mm, 1, @CurrentDate)
        END

然后，修改您的查询以加入该表:

Then, modify your query to join against this table:

SELECT      ALLItems.ItemId,
            SUM(COALESCE(Inventory.Qty, 0)) AS Individual_MonthQty,
            MONTH(#AllDates.ThisDate) AS Individual_MonthAsNumber,
            DATENAME(MONTH, #AllDates.ThisDate) AS Individual_MonthAsString
FROM        #AllDates
            JOIN (SELECT DISTINCT dbo.Inventory.ItemId FROM dbo.Inventory)  AS ALLItems ON 1 = 1
            LEFT JOIN Inventory ON DATEADD(dd, - DAY(Inventory.dadded) +1, Inventory.dadded) = #AllDates.ThisDate AND ALLItems.ItemId = dbo.Inventory.ItemId
WHERE       
            #AllDates.ThisDate >= @StartRange
AND         #AllDates.ThisDate <= @EndRange
GROUP BY    ALLItems.ItemId, 
            #AllDates.ThisDate

那么你应该每个月都有一个记录，不管它是否存在于 Inventory 中.

Then you should have a record for each month, regardless of whether it exists in Inventory.

这篇关于SQL 组和按月求和 - 默认为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！