Hi,

I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key.

Using a SELECT statement is it possible to retrieve the count and number ofrecords for each day e.g 2007-10-25 for all red, and all blue etc

Thanks.
Neil
__________________________________________________ _______________
Feel like a local wherever you go.
http://www.backofmyhand.com

Hi Neil,

Try this:

SELECT LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME, COUNT(T.*) AS CNT FROM
TABLE_NAME GROUP BY LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME.

Post the table structure if this does not .

Regards,

Ravi

On 10/27/07, Neil Tompkins <neildtompkins@hotmail.com> wrote:
>
> Hi,
>
> I have a table of records all of which have a timestamp against them like
> 2007-10-25 10:10:19, and category like red, blue etc and a unique key.
>
> Using a SELECT statement is it possible to retrieve the count and number
> of records for each day e.g 2007-10-25 for all red, and all blue etc
>
> Thanks.
> Neil
> __________________________________________________ _______________
> Feel like a local wherever you go.
> http://www.backofmyhand.com

Neil,

Do you mean ...

SELECT DATE(datetimecol) AS date,colourcol,COUNT(*)
FROM tbl
GROUP BY date,colourcol;

PB

------

Neil Tompkins wrote:
> Hi,
>
> I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key.
>
> Using a SELECT statement is it possible to retrieve the count and number of records for each day e.g 2007-10-25 for all red, and all blue etc
>
> Thanks.
> Neil
> __________________________________________________ _______________
> Feel like a local wherever you go.
> http://www.backofmyhand.com
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: 10/26/2007 8:50 AM
>

Sorry a correction....

SELECT DISTINCT LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME, COUNT(T.*) AS
CNT FROM TABLE_NAME T GROUP BY LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME

Ravi

On 10/28/07, Peter Brawley <peter.brawley@earthlink.net> wrote:
>
> Neil,
>
> Do you mean ...
>
> SELECT DATE(datetimecol) AS date,colourcol,COUNT(*)
> FROM tbl
> GROUP BY date,colourcol;
>
> PB
>
> ------
>
> Neil Tompkins wrote:
> > Hi,
> >
> > I have a table of records all of which have a timestamp against them
> like 2007-10-25 10:10:19, and category like red, blue etc and a unique key.
> >
> > Using a SELECT statement is it possible to retrieve the count and number
> of records for each day e.g 2007-10-25 for all red, and all blue etc
> >
> > Thanks.
> > Neil
> > __________________________________________________ _______________
> > Feel like a local wherever you go.
> > http://www.backofmyhand.com
> >
> > ------------------------------------------------------------------------
> >
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date:
> 10/26/2007 8:50 AM
> >
>

> > Neil Tompkins wrote:
> > > I have a table of records all of which have a timestamp against them
> > like 2007-10-25 10:10:19, and category like red, blue etc and a unique key.
> > >
> > > Using a SELECT statement is it possible to retrieve the count and number
> > of records for each day e.g 2007-10-25 for all red, and all blue etc

Hi neil.

I would use
SELECT count(colour_field_name) AS Counter,
DATE_FORMAT(datetimecol,'%y-%m-%d') as Date, colour_field_name FROM
table_name GROUP BY Date, colour_field_name;

Regards,
Ranjeet Walunj