> SELECT DATE_FORMAT( date_column, '%Y- %m- %d') AS mydate, > COUNT(*) AS c > FROM table > GROUP BY mydate This wokes. Thank you ignatius! I have worked on the charts and it helped me to get a lot further. There is a point where I still have not found a good solution. Problem is, that there are some days where no data has changed (for example a day with no new registered members). In this case the query should return '0' E.g.: Jan 10 Feb 32 Mar 0 Apr 5 At the moment I would not have march listed in my chart which causes problems. E.g.: Jan 10 Feb 32 Apr 5 Do u have an idea on how to include the 0? Thanx for any help on that. merlin -- Worldwide Travel Community http://www.globosapiens.net "Ignatius Reilly" <ignatius.reilly@free.fr> schrieb im Newsbeitrag 091a01c311a6$73f70e00$0100a8c0@server">news:091a01c311a6$73f70e00$0100a8c0@server... > 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: 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 > > 000801c3101f$59d88ae0$7c02a8c0@coconut">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 > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
