CODEWITHSUNDEEP
sqlt-sql
Riz
Riz

Reputation: 6676

counting number of entries in a month

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?

Upvotes: 3

Views: 3317

Answers (3)

Frank Kalis
Frank Kalis

Reputation: 1352

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

Upvotes: 0

Kaleb Brasee
Kaleb Brasee

Reputation: 51925

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

Upvotes: 1

jason
jason

Reputation: 241581

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.

Upvotes: 6

Related Questions