Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Include missing months in Group By query

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.

like image 836
Michael Nelson Avatar asked Jul 14 '12 00:07

Michael Nelson


People also ask

How do I select a specific month record in SQL?

Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column. (In our example, we use the start_date column of date data type).

How do I get next 3 months data in SQL?

In SQL Server, you can use the DATEADD() function to get last 3 months (or n months) records.


3 Answers

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;
like image 66
Aaron Bertrand Avatar answered Nov 16 '22 02:11

Aaron Bertrand


Since your query Just Can't guess the months you want, you will need to have the Months that you want stored in somewhere, Join them with your table, and then group. Something like:

;With Months (Month) 
AS
(

    select 'January' as Month
    UNION
    select 'February' as Month
    UNION
    select 'March' as Month
    UNION
    select 'April' as Month
    UNION
    select 'May' as Month
    UNION
    select 'June' as Month
    UNION
    select 'July' as Month
    UNION
    select 'August' as Month
    UNION
    select 'September' as Month
    UNION
    select 'October' as Month
    UNION
    select 'November' as Month
    UNION
    select 'December' as Month

)
--Also you could have them in a "Months" Table

Then Just JOIN this table with your table:

   Select 
    SELECT datename(month, OrderDate) as Month, COUNT(OrderNumber) 
    FROM Months T1
    LEFT JOIN OrderTable T2 on datename(month, T2.OrderDate) = T2.Month
    WHERE (T2.OrderDate >= '2012-01-01' and T2.OrderDate <= '2012-06-30') 
OR T2.OrderDate IS NULL --So will show you the months with no rows
    GROUP BY year(T2.OrderDate), month(T2.OrderDate), datename(month, T2.OrderDate)

Hope it works!

like image 45
Gonzalo.- Avatar answered Nov 16 '22 02:11

Gonzalo.-


Here is one using recursive CTE:

declare @StartDate datetime = '2015-04-01';
declare @EndDate datetime = '2015-06-01';

-- sample data
declare @orders table (OrderNumber int, OrderDate datetime);
insert into @orders
select 11, '2015-04-02'
union all
select 12, '2015-04-03'
union all
select 13, '2015-05-03'
;

-- recursive CTE
with dates
as (
    select @StartDate as reportMonth
    union all
    select dateadd(m, 1, reportMonth)
    from dates
    where reportMonth < @EndDate
    )
select 
    reportMonth,
    Count = count(o.OrderNumber)
from dates
left outer join @orders as o 
    on o.OrderDate >= reportMonth
    and o.OrderDate < dateadd(MONTH, 1, reportMonth)
group by 
    reportMonth
option (maxrecursion 0);
;
like image 24
rpisryan Avatar answered Nov 16 '22 01:11

rpisryan