SQL Query to get Count records for every month wise in a year

 

Description:

In this example we explain that how to get all Month  like Jan,Feb, Dec etc.. Using SQL Query. or how to get download count per month of the year using SQL Query. Or count records of every month in a year using SQL.or SQL query to get month wise count and display it on website or chart. or how to get count of month based on a year using SQL query.

We have requirement like display Month wise download application count in website. So achieve this below is the SQL Query that will helpful for you to get month wise download count.

 

Query:

;WITH theDates AS

     (SELECT DATEADD(month,-12,DATEADD(hh, +05.30, getdate()))  as theDate

      UNION ALL

      SELECT DATEADD(month, 1, theDate)

        FROM theDates

       WHERE DATEADD(month, 1, theDate) <= DATEADD(day,0,DATEADD(hh, +05.30, getdate()))

     )

 

   SELECT DATENAME(month,CONVERT(DATE, theDate))as DownloadDate,COUNT(TotalCount) As TotalDownLoad

              FROM theDates t Left join  AppCounts ac on CONVERT(DATE, theDate)=ac.DownloadDate

      left join Application ap on ac.ApplicationId = ap.ApplicationId

      GROUP BY  CONVERT(DATE, theDate)