CODEWITHSUNDEEP
mysqlsqlmariadb
AnApprentice
AnApprentice

Reputation: 110960

Query for # of new user per week - how to order by year/week?

I'm working to write a MySQL query that outputs the number of new users created by week.

My user table is:

id | created_at

My query:

SELECT YEAR(created_at) AS Year, DATE_FORMAT(created_at, '%b %e') AS Week, COUNT(*) AS total
FROM users
GROUP BY Year, Week;

The problem:

Where I'd like the year and week sorted.

Upvotes: 0

Views: 253

Answers (1)

fubar
fubar

Reputation: 17388

I think you'll find the function YEARWEEK() helpful, not only for the grouping, but also for the ordering.

Also, your use of DATE_FORMAT() doesn't look right, because you're outputing the %e, which is the day of the month, yet you're grouping by week?

SELECT DATE_FORMAT(created_at, '%Y %b %v') AS date, COUNT(*) AS total
FROM users
GROUP BY YEARWEEK(created_at)
ORDER BY YEARWEEK(created_at) DESC;

Upvotes: 2

Related Questions