Re: difficult count statement

What you can do: - create an auxiliary table months( char(2) not null ) - feed it with values '01', ..., '12' - left join table 'months' with your result table, grouped by month. Then you will have all months. It is always handy to have a few such auxiliary tables in your DB, like "years, "months", "weeks", "year_weeks", "ordinals", ... HTH Ignatius ____________________________________________ ----- Original Message ----- From: "Merlin" <news.groups@web.de> To: <php-db@lists.php.net> Sent: Monday, May 05, 2003 5:32 PM Subject: Re: [PHP-DB] difficult count statement > > 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 > 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: [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 > > > > > > > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >