在Group By查询中包含缺失的月份 [英] Include missing months in Group By query

问题描述

我想我在这里遇到了一个难题：（$ / b>

我试图按月计算订单数，即使是零。 ：

  SELECT datename（month，OrderDate）as Month，COUNT（OrderNumber）AS Orders 
 FROM OrderTable 
 WHERE OrderDate> ='2012-01-01'和OrderDate< ='2012-06-30'
 GROUP BY year（OrderDate），month（OrderDate），datename（month，OrderDate）
  

我期望得到的是这样的：

 月份订单
 ----- ------ 
 1月10日
 2月7日
 3月0日
 4月12日
 5月0 
 6月5日
  

...但我的查询我尝试了 COALESCE（COUNT（OrderNumber），0）和 ISNULL（COUNT（OrderNumber），0） 但我敢肯定，这个分组导致了这个问题无法解决。

和。

  DECLARE @StartDate SMALLDATETIME，@EndDate SMALLDATETIME; 
 
 SELECT @StartDate ='20120101'，@EndDate ='20120630'; 
 
; 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 
 [Month] = DATENAME（MONTH，dd），
 [Year] = YEAR（dd），
 OrderCount = COUNT（ o.OrderNumber）
 FROM d LEFT OUTER JOIN dbo.OrderTable AS o 
 ON o.OrderDate> = dd 
 AND o.OrderDate< DATEADD（MONTH，1，d.d）
 GROUP BY d.d 
 ORDER BY d.d; 
  

I think I have a tough one here... :(

I am trying to get an order count by month, even when zero. Here's the problem query:

SELECT datename(month, OrderDate) as Month, COUNT(OrderNumber) AS Orders
FROM OrderTable
WHERE OrderDate >= '2012-01-01' and OrderDate <= '2012-06-30'
GROUP BY year(OrderDate), month(OrderDate), datename(month, OrderDate)

What I'm looking to get is something like this:

Month            Orders
-----            ------
January          10
February         7
March            0
April            12
May              0
June             5

...but my query skips a row for March and May. I've tried COALESCE(COUNT(OrderNumber), 0) and ISNULL(COUNT(OrderNumber), 0) but I'm pretty sure the grouping is causing that not to work.

解决方案

This solution doesn't require you to hard-code the list of months you might want, all you need to do is provide any start date and any end date, and it will calculate the month boundaries for you. It includes year in the output so that it will support more than 12 months and so that your start and end dates can cross a year boundary and still order correctly and show the correct month and year.

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20120101', @EndDate = '20120630';

;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 
  [Month]    = DATENAME(MONTH, d.d), 
  [Year]     = YEAR(d.d), 
  OrderCount = COUNT(o.OrderNumber) 
FROM d LEFT OUTER JOIN dbo.OrderTable AS o
  ON o.OrderDate >= d.d
  AND o.OrderDate < DATEADD(MONTH, 1, d.d)
GROUP BY d.d
ORDER BY d.d;

这篇关于在Group By查询中包含缺失的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！