Count records per day

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Count records per day

Post 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?!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count records per day

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Re: Count records per day

Post 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*
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count records per day

Post by VladSun »

What about this one:
[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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Re: Count records per day

Post by JayBird »

That works a treat! Thanks

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