Return number of tickets created per week with SQL query

I have a table of tickets, common_ticket, with a column called creation_date, which holds the date of creation.

I want to count how many tickets were created each week for the past few months. I am having trouble writing a SQL query to return such information. How it is returned is not really important as long as there is a distinct numb开发者_如何学Cer for each separate week.

Does anyone have any ideas on how to do this?

---

Something like:

SELECT extract(week from creation_date), 
       extract(year from creation_date),
       count(*)
FROM tickets
GROUP BY extract(week from creation_date), 
         extract(year from creation_date)

---

this should do it:

SELECT [t1].[value] AS [Year], [t1].[value2] AS [Week], COUNT(*) AS [Count]
FROM (
    SELECT DATEPART(Year, [t0].[creation_date]) AS [value],
        DATEPART(Week, [t0].[creation_date]) AS [value2]
    FROM [common_ticket] AS [t0]
) AS [t1]
GROUP BY [t1].[value], [t1].[value2];