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
