GROUP BY&缺少MONTHS的值的总和 [英] GROUP BY & SUM of values with missing MONTHS

问题描述

我仔细阅读了许多示例，并加入了几个示例，以得出以下结论；

I have gone through a lot of examples and joined couple of them in order to come down to the following statement;

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20170930', @EndDate = '20180930';

;WITH d(d) AS 
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)

SELECT 
  [Period]    = CONVERT(VARCHAR(4), YEAR(d.d)) + '-' + CONVERT(VARCHAR(2), MONTH(d.d)),
  QtyTotal = ISNULL(SUM(o.QEXIT),0)
FROM d LEFT OUTER JOIN VE_STOCKTRANS AS o
  ON o.TRANSDATE >= d.d

  AND o.TRANSDATE < DATEADD(MONTH, 1, d.d)
 WHERE STOCKID = 6000 AND TRANSTYPE = 3553
GROUP BY d.d
ORDER BY d.d;

我需要获取过去一年中某商品的总销售质量.如果该商品在该特定月份没有任何销售，则该月旁边应显示0.除非提供WHERE子句，否则上面的查询将执行所需的操作.一旦添加WHERE子句以获取特定产品的数据，就没有销售的月份消失了.

I need to get the total sales quaantity of an item for the past year. If the item does not have any sales for that particular month, 0 should be displayed next to that month. The above query does what is required unless the WHERE clause is provided. As soon as I add the WHERE clause to get the data for a specific product, the months with no sales dissappears.

如果有经验的SQL开发人员可以向我显示正确的方向，我将不胜感激.

I would be grateful if an experienced SQL developer can show me the right direction on this.

谢谢

推荐答案

您需要将条件移至 ON :

-- ...
SELECT 
  [Period] = CONVERT(VARCHAR(4),YEAR(d.d)) +'-'+ CONVERT(VARCHAR(2), MONTH(d.d)),
  QtyTotal = ISNULL(SUM(o.QEXIT),0)
FROM d LEFT OUTER JOIN VE_STOCKTRANS AS o
  ON o.TRANSDATE >= d.d

  AND o.TRANSDATE < DATEADD(MONTH, 1, d.d)
  AND STOCKID = 6000 AND TRANSTYPE = 3553   -- here
GROUP BY d.d
ORDER BY d.d;

这篇关于GROUP BY&缺少MONTHS的值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！