counting number of entries in a month

Question

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

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 reference, see month and year commands in 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

What about using some 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