Counting the number of records per month

Question

I have an order table and it has a datetime column called order_date. I want to calculate the number of orders for each month in 2009. How should I do it?

Answer 1

select month(order_date) as orderMonth, count(*) as orderCount
from order
where year(order_date) = 2009
group by month(order_date)
order by month(order_date)

For help, see. month, And yearin Transact-SQL.

Answer 2

SELECT MONTH(order_date) AS ordermonth, 
   COUNT(*) AS ordercount 
FROM order 
WHERE YEAR(order_date) = 2009 
GROUP BY ordermonth;

Answer 3

How about using a neat DATETIME trick?

select 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0) AS orderMonth, count(*) as orderCount
from 
    [order]
where 
    order_date >= '2009-01-01'
group by 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0)
order by 
    orderMonth