Q:

Get 12 month name with data count using SQL Server?

I have created a query to get 12 months with name and also get count for the month. Using my query, I am getting data month-wise and also getting month name.
But here in my query, if the month of June doesn't have any data in the table, I won't get an entry for the month of June. I want the month of June to also be shown, with 0 data. How can do that? I have no idea.
Here is my query:
DECLARE @year nvarchar(max)
SELECT  @year = year(getdate())

SELECT  
    MONTH(InsertDateTime) AS m,
    FORMAT(InsertDateTime, 'MMM-yy') AS Month,
    COUNT(InsertDateTime) AS tally
FROM
    Comments
WHERE  
    YEAR(InsertDateTime) = @year
GROUP BY 
    FORMAT(InsertDateTime, 'MMM-yy'), MONTH(InsertDateTime)

This is my return o/p:
m | Month | tally
1   Jan-17    1
2    Feb-17   1 
3    Mar-17   10 
4    Apr-17   15  
5    May-17   20
8    Aug-17   25

This is my expected o/p:
m | Month | tally
1   Jan-17    1
2    Feb-17   1 
3    Mar-17   10 
4    Apr-17   15  
5    May-17   20
6    June-17  0
7    July-17  0
8    Aug-17   25
9    Sep-17    0
10   Oct-17    0
11   Nav-17    0
12   Dec-17    0

This return data is correct, but here I don't return other month. Like june,july,sep,oct,nav,dec mont entry is not availbale in the table. I want this remining month also with tally coulmn in the 0 value.

A:

Using an adhoc calendar table to generate 12 months:
/* @StartDate = truncate `getdate()` to the start of the year: */
declare @StartDate datetime = dateadd(year , datediff(year , 0, getdate()), 0)

;with Months as (
select top (12) 
     m = row_number() over (order by number)
   ,[Month] = dateadd(month, row_number() over (order by number) -1, @StartDate)
  , NextMonth = dateadd(month, row_number() over (order by number), @StartDate)
  from master.dbo.spt_values
)
select 
    m.m
  , Month = format(m.Month, 'MMM-yy')
  , tally = count(c.InsertDateTime)
from Months m
  left join Comments c
    on c.InsertDateTime >= m.Month
   and c.InsertDateTime < m.NextMonth
group by m.m, format(m.Month, 'MMM-yy')
order by m

rextester demo: http://rextester.com/NNVI43016
returns:
+----+--------+-------+
| m  | Month  | tally |
+----+--------+-------+
|  1 | Jan-17 |     3 |
|  2 | Feb-17 |     0 |
|  3 | Mar-17 |     2 |
|  4 | Apr-17 |     0 |
|  5 | May-17 |     0 |
|  6 | Jun-17 |     0 |
|  7 | Jul-17 |     0 |
|  8 | Aug-17 |     0 |
|  9 | Sep-17 |     0 |
| 10 | Oct-17 |     0 |
| 11 | Nov-17 |     0 |
| 12 | Dec-17 |     1 |
+----+--------+-------+

This has the added advantage in that it is not calling functions on the columns in the larger table Comments, and it is using SARGable conditions for the join.
Reference:

Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
SARGable functions in SQL Server - Rob Farley
SARGable expressions and performance - Daniel Hutmachier
Generate a set or sequence without loops - 1 - Aaron Bertrand
Generate a set or sequence without loops - 2 - Aaron Bertrand
Generate a set or sequence without loops - 3 - Aaron Bertrand
The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden

