Display count of records month wise in SQL Server

Bhavesh23
 
on Sep 10, 2022 02:03 AM
401 Views

hello,

I want to display month wise count from SQL Server record.

Month.      Record

Jan              100

Feb              150

March           250

.....

I want to count no of record month wise and i want to display that no of count like jan, feb, march...Dec etc.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 10, 2022 02:15 AM

Hi Bhavesh23,

Refer below query.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

SELECT LEFT(DATENAME(MONTH, OrderDate), 3) 'Month.',COUNT(*) 'Record' 
FROM Orders 
GROUP BY LEFT(DATENAME(MONTH, OrderDate), 3)
ORDER BY
CASE 
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Jan' THEN 0
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Feb' THEN 1
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Mar' THEN 2
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Apr' THEN 3
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'May' THEN 4
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Jun' THEN 5
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Jul' THEN 6
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Aug' THEN 7
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Sep' THEN 8
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Oct' THEN 9
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Nov' THEN 10
	WHEN LEFT(DATENAME(MONTH, OrderDate), 3) = 'Dec' THEN 11
END

Output

Month. Record
Jan 75
Feb 72
Mar 91
Apr 82
May 33
Jun 23
Jul 45
Aug 44
Sep 45
Oct 173
Nov 78
Dec 69

Then use the query to display in chart.