Page 1 of 1

Count records per day

Posted: Fri Jun 06, 2008 4:53 am
by JayBird
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?!

Re: Count records per day

Posted: Fri Jun 06, 2008 6:33 am
by VladSun
[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.

Re: Count records per day

Posted: Fri Jun 06, 2008 6:41 am
by JayBird
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

Posted: Fri Jun 06, 2008 6:48 am
by VladSun
What about this one:
[sql]SELECT    count(id) AS rcount,    date(FROM_UNIXTIME(reg_date)) AS rdateFROM    userGROUP BY    rdate[/sql]

Re: Count records per day

Posted: Fri Jun 06, 2008 6:54 am
by JayBird
That works a treat! Thanks

Im sure the query i did originally was WAAAAY more complicated than that :crazy: