Tag Archives: Monthly Average Data in Sql Server

How to get Monthly Data in Sql Server

This article demonstrate how to get Monthly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different Monthly aggregated sales data formats.

Monthly Data in Sql Server
[ALSO READ] How to get Quarterly Data in Sql Server

Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in past 0-798 days as the sales date by using the below script.

--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0-798 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (798+1)*RAND()),GETDATE()))
GO 1000

Demo 1: Getting Monthly Data

SELECT YEAR(SalesDate) [Year], MONTH(SalesDate) [Month], 
 DATENAME(MONTH,SalesDate) [Month Name], COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate), MONTH(SalesDate), 
 DATENAME(MONTH, SalesDate)
ORDER BY 1,2

RESULT:
Monthly Average Sales Data in Sql

Demo 2: Getting Monthly Data using PIVOT

SELECT *
FROM (SELECT YEAR(SalesDate) [Year], 
       DATENAME(MONTH, SalesDate) [Month], 
       COUNT(1) [Sales Count]
	  FROM #Sales
      GROUP BY YEAR(SalesDate), 
	  DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count]) 	
	FOR Month IN ([January],[February],[March],[April],[May],
	[June],[July],[August],[September],[October],[November],
	[December])) AS MNamePivot

RESULT:
Monthly Sum Data in Sql Server Using PIVOT

[ALSO READ]:
How to get Yearly data in Sql Server
How to get Quarterly Data in Sql Server
How to get Daily Data in Sql Server
How to get Hourly data in Sql Server