No records for given month in SQL COUNT and GROUP BY Month

Question

I have the following set of data:

ID  |  CREATED     |  USER
--------------------------
1   |  2012-01-14  |   XYZ
2   |  2012-03-14  |   XYZ
3   |  2012-03-15  |   XYZ
4   |  2012-03-24  |   ABC
5   |  2012-04-10  |   XYZ
6   |  2012-04-11  |   ABC

And I need a report that shows the COUNT for a given user but also with a 0 for months where there are no records.

MTH  |  COUNT
-------------
JAN  |   1
FEB  |   0
MAR  |   2
APR  |   1

I managed to get it to work but without the 0 for months with no records. So far, I have this syntax which is throwing an error..

SELECT Month(CREATED), COUNT(SELECT * FROM SEARCHES WHERE USER = 'XYZ')
FROM SEARCHES
GROUP BY Month(CREATED)

score 2 · Answer 1 · edited May 23 '17 at 12:11

You can't query for data that is not there. So you need to join to table that either lists (or simulates listing) all the months you want to join to. How to do that can depend on the rdbms you are using. Using rownum to generate a list which can be converted into months is a common approach. Or Postgres has a function to generate a series. As an aside, I don't really understand why the sub-select is in the column clause rather than adding the filter to the main query.

See also:

SQL frequency distribution query to count ranges with group-by and include 0 counts

How to generate list of all dates between sysdate-30 and sysdate+30?

score 2 · Accepted Answer · answered Apr 04 '12 at 23:05

Here is general query for grouping by date range. @startDate and @endDate define the range; you might set min(created) and max(created) for user, or some fixed range.

CTE monthlyRange generates table of months - first of current month, first of next month. Searches is later left-joined to monthlyRanges by created. Note that user is filtered in join as this is left join and filtering in where clause would effectively turn it into inner join.

declare @startDate datetime
declare @endDate datetime
set @startDate = '2012-01-01'
set @endDate = '2013-01-01'

; with monthlyRange (startMonth, startNextMonth) as (
  select dateadd (m, datediff (m, 0, @startDate), 0),
         dateadd (m, datediff (m, 0, @startDate) + 1, 0)
  union all
  select dateadd (m, 1, startMonth),
         dateadd (m, 1, startNextMonth)
    from monthlyRange
  where startNextMonth <= dateadd (m, datediff (m, 0, @endDate), 0)
)
SELECT Year(monthlyRange.startMonth) Year, 
       Month(monthlyRange.startMonth) Month, 
       COUNT(searches.Created) Count
  FROM monthlyRange 
  left join SEARCHES
    on monthlyRange.startMonth <= Searches.Created
   and monthlyRange.startNextMonth > Searches.Created
   and [USER] = 'XYZ'
GROUP BY Year(monthlyRange.startMonth), Month(monthlyRange.startMonth)
order by 1, 2

Here is Sql Fiddle for testing

This worked great! Thanks also for introducing me to SQL fiddle. — greener, Apr 05 '12 at 17:34

score 0 · Answer 3 · answered Apr 04 '12 at 23:03

Try this:

CREATE TABLE #DaTable(
  ID    INT,
  CREATED   DATE,
  USER_ VARCHAR(10)
)
INSERT INTO #DaTable(ID, CREATED, USER_) VALUES
(1  , '2012-01-14', 'XYZ'),
(2   ,  '2012-03-14'  ,   'XYZ'),
(3   ,  '2012-03-15'  ,   'XYZ'),
(4   ,  '2012-03-24'  ,   'ABC'),
(5   ,  '2012-04-10'  ,   'XYZ'),
(6   ,  '2012-04-11'  ,   'ABC')

CREATE TABLE #DaMonths(
  Nr    INT,
  Name  VARCHAR(10)
)
INSERT INTO #DaMonths(Nr, Name) VALUES
(1, 'Jan'),
(2, 'Feb'),
(3, 'Mar'),
(4, 'Apr'),
(5, 'May'),
(6, 'Jun'),
(7, 'Jul'),
(8, 'Aug'),
(9, 'Sep'),
(10, 'Oct'),
(11, 'Nov'),
(12, 'Dec')

SELECT M.Nr, M.Name, COUNT(DT.ID) as Count_
FROM #DaMonths as M
  LEFT OUTER JOIN #DaTable as DT ON
    M.Nr = Month(DT.CREATED)
GROUP BY M.Nr, M.Name
ORDER BY M.Nr

No records for given month in SQL COUNT and GROUP BY Month

3 Answers3