Re: difficult count statement

Then you may try: SELECT DATE_FORMAT( date_column, '%Y- %m- %d') AS mydate, COUNT(*) AS c FROM table GROUP BY mydate Ignatius ____________________________________________ ----- Original Message ----- From: "Merlin" <news.groups@web.de> To: <php-db@lists.php.net> Sent: Saturday, May 03, 2003 8:23 PM Subject: Re: [PHP-DB] difficult count statement > > SELECT date_column, COUNT(*) AS c FROM table GROUP BY date_column > > > > Will give you a count for each date in the table. > > Thank you. This works great. There is just one important prblem. This > coulumn contains datetime. So it cant be grouped to get the amounts of reg > users for each day. It gives me always 1 for a specific time. > > E.g: > date:2002-07-29 17:21:00members:1 > date:2002-07-29 17:21:50members:1 > date:2002-07-29 17:23:05members:1 > > I played around now for 1/2 hour and could not figure out how to change this > into what I want. Can you give me some more help on this? > > Thank you in advance, > > Merlin > > > > -- > Worldwide Travel Community > http://www.globosapiens.net > "John W. Holmes" <holmes072000@charter.net> schrieb im Newsbeitrag > news:000801c3101f$59d88ae0$7c02a8c0@coconut... > > > I am running a mysql db and I would like to figure out how to count > > member > > > growth. > > > > > > For a chart I would need the number of members who have registered > > each > > > day. > > > > > > Something like this: > > > count (*) AS c > > > WHERE > > > regdate = '20030501' > > > > > > does not really make sense, since I would have to query the db for > > each > > > day > > > making it dramaticly slow. > > > Has anybody an idea on how to get the numbers registered each day to > > fead > > > a > > > graph with data? > > > > SELECT date_column, COUNT(*) AS c FROM table GROUP BY date_column > > > > Will give you a count for each date in the table. If you want it for a > > range, then use: > > > > SELECT date_column, COUNT(*) AS c FROM table WHERE date_column BETWEEN > > 20030101 AND 20030201 GROUP BY date_column > > > > Substitute your own dates, of course. > > > > ---John W. Holmes... > > > > PHP Architect - A monthly magazine for PHP Professionals. Get your copy > > today. http://www.phparch.com/ > > > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >