Posted by Paul Lautman on 07/06/06 17:37

Bob Bedford wrote:
> Hi all,
>
>> SELECT count( DISTINCT a.idperson ) , b.region
>> FROM `table1` a
>> JOIN `table2` b
>> USING ( zip )
>> GROUP BY region
>
> Now I've an other question, as I can't get it to work, even with your
> example.
>
> I've a table where there is a datetime field.
>
> I'd like to count the number of articles created every day. How to do
> so ?
> I've tried this:
> select distinct date_format(DateTimeCreation,'%d %m %Y'),
> count(distinct idarticle) from articles group by DateTimeCreation
> order by DateTimeCreation ASC
>
> What I'd like is a table with
> date nbre of creation
> 2006-01-01 3
> 2006-01-02 2
> 2006-01-03 5
> 2006-01-04 7
>
> and so on.....
> actually I've:
> DateTimeCreation
> 2006-01-01 09:11:03
> 2006-01-01 12:01:22
> 2006-01-01 18:42:17
> 2006-01-02 10:27:55
> ....
>
> Thanks for help.
>
> Bob
> select distinct date_format(DateTimeCreation,'%d %m %Y'),
> count(distinct idarticle) from articles group by DateTimeCreation
> order by DateTimeCreation ASC
I'm a bit confused by a bit of this. You say that you want
date nbre of creation
2006-01-01 3
but the date format in your query is day month year?
Try
SELECT count( idarticle ) , left( DateTimeCreation, 10 ) AS created
FROM `articles`
GROUP BY created
or
SELECT count( idarticle ) , date_format(DateTimeCreation,'%d %m %Y') AS
created
FROM `articles`
GROUP BY created

Either should work

[Back to original message]