CODEWITHSUNDEEP
pythonmysql
Bill Noble
Bill Noble

Reputation: 6734

MySQL query to count number of entries in a table by date and grouped by day/week/month

I want to count the number of users in my MySQL auth_users table by the date they are added and group them into users per day, per week, and per month.

The only way I can think of to do this is to do a simple MySQL query like this:

SELECT date_joined FROM users

And then write some Python to iterate through the table checking for specific date periods.

I was wondering though if there are any MySQL queries or Python utilities to do this?

EDIT

I have tried the answer pointed to but it isn't working for me. I have tried the query:

SELECT COUNT(id) FROM ebdb.auth_user GROUP BY date_joined.YEAR, date_joined.MONTH

date_joined is a DATETIME field in my database table ebdb.auth_user. I get an MySQL error:

Unknown column 'date_joined.YEAR'

Upvotes: 0

Views: 414

Answers (1)

gagardne
gagardne

Reputation: 46

Try the MySQL EXTRACT function.

SELECT COUNT(id), EXTRACT(MONTH FROM date_joined) as month, EXTRACT(YEAR FROM date_joined) as year
FROM auth_user
GROUP BY month, year

Upvotes: 3

Related Questions