CODEWITHSUNDEEP
sqlazure-sql-database
Divashen24
Divashen24

Reputation: 35

How to count the number of occurrences per month?

I have a program and want to generate reports from it. The program is for a grocery store that does deliveries. A customer places an order and the program captures the various items that the customer wishes to purchase, e.g. Order 21 and the program lists the various items relating to that specific order.

I would like to generate a SQL query that counts the number of orders that customers place each month and want it to look like this 

No of orders Month
10           Jan
20           Feb
30           March

The SQL that I had which is 

SELECT COUNT(OrderID) AS "Number Of Orders", datepart(month, order_date) AS "Month"
FROM "ORDER"
Group by datepart(month, order_date);

Displays

Number of Orders  Month
16                9

However this is the count of all the orders for the various months and is only displayed in month 9 (September.)

Upvotes: 3

Views: 6532

Answers (1)

Mahbubur Rahman Manik
Mahbubur Rahman Manik

Reputation: 5161

Hope this will help: 

select COUNT(OrderID) as "Number Of Orders",DATENAME(mm,order_date) as "Month" from "ORDER" group by DATENAME(mm,DueDate) order by 2

Upvotes: 1

Related Questions