Last 12 months, group by week

I have a table with a column REGDATE, a registration date (YYYY-MM-DD HH:MM:SS). I would like to show an histogram (ExtJS) in order to understand in which period of the years users are signing up. I wou开发者_如何学JAVAld like to do this for the past twelve months with respect to the current date and to group dates by week.

Any hints?

FWIW in PostgreSQL, Karaszi has an answer that works, but there is a faster query:

SELECT date_trunc('week', REGDATE) AS "Week" , count(*) AS "No. of users"
FROM <<TABLE>>
WHERE REGDATE > now() - interval '12 months' 
GROUP BY 1
ORDER BY 1;

I based this off the work of Ben Goodacre

in MySQL:

SELECT COUNT(*), DATE_FORMAT(regdate, "%X%V") AS regweek FROM table GROUP BY regweek;

or

SELECT COUNT(*), YEARWEEK(NOW(), 2) as regweek FROM table GROUP BY regweek;

in PostgreSQL:

SELECT COUNT(*), EXTRACT(YEAR FROM regdate)::text ||  EXTRACT(WEEK FROM regdate)::text AS regweek FROM table GROUP BY regweek;

Maybe this?

select to_char(REGDATE,'WW') "Week number",
       count(*) "number of signups",
from YOUR_TABLE
where REGDATE > current_date-365
group by to_char(REGDATE,'WW')
order by to_char(REGDATE,'WW')

Hint: (SQL)

SELECT CONVERT (VARCHAR(7), REGDATE, 120) AS [RegistrationMonth]
FROM ...
GROUP BY CONVERT (VARCHAR(7), REGDATE, 120)
ORDER BY CONVERT (VARCHAR(7), REGDATE, 120)