A couple of months ago I created a query that would count the number of records per day.
I have a `users` table in which I store a unix timestamp of when they registered.
I wanted to count the number of registration per day so I can graph it.
As i said, i did create a query for this a couple of months ago, but after a hard drive crash, i lost it, and i cant for the life of me remember how i did it.
Any ideas?!
Count records per day
Moderator: General Moderators
Re: Count records per day
[sql]SELECT count(id) AS rcount, date(reg_date) AS rdateFROM userGROUP BY rdate [/sql]
The only issue is that if you don't have registrations on a particular date it won't be displayed.
The only issue is that if you don't have registrations on a particular date it won't be displayed.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Count records per day
Yeah, i thought it might be something like that, but this is the result i get. Where does the '3645 NULL' come from? Also, There are registratiosn in the future!?
Code: Select all
3645 NULL
1 2012-00-00
1 2012-00-04
3 2012-00-05
1 2012-00-06
1 2012-00-07
1 2012-00-26
1 2012-01-00
1 2012-01-02
1 2012-01-11
*snip*
Re: Count records per day
What about this one:
[sql]SELECT count(id) AS rcount, date(FROM_UNIXTIME(reg_date)) AS rdateFROM userGROUP BY rdate[/sql]
[sql]SELECT count(id) AS rcount, date(FROM_UNIXTIME(reg_date)) AS rdateFROM userGROUP BY rdate[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
Re: Count records per day
That works a treat! Thanks
Im sure the query i did originally was WAAAAY more complicated than that
Im sure the query i did originally was WAAAAY more complicated than that