Help with count (mysql)

I'm looking to extract data from a database of forum posts.

The query I have so far is

SELECT FROM_UNIXTIME(post_time, '%d-%m-%Y') FROM `phpbb_posts` WHERE `poster_id` = '3' ORDER BY `post_time` ASC

Code (markup):

This works fine, but that's not how I want the results returned. Right now it returns the dates like this:

01-01-2007
02-01-2007
02-01-2007
03-01-2007
03-01-2007
03-01-2007

Code (markup):

But I want it returned like this

01-01-2007 1
02-01-2007 2
03-01-2007 3

Code (markup):

Etc.. the first column (obviously) is the date, and the 2nd column is the count of those dates in the table.

Thanks

 

Luckily this is quite easy in SQL. The code you need is:

SELECT FROM_UNIXTIME(post_time, '%d-%m-%Y'), COUNT(*) FROM `phpbb_posts` WHERE `poster_id` = '3' ORDER BY `post_time` ASC
GROUP BY FROM_UNIXTIME(post_time, '%d-%m-%Y')

Let me know if you have any problems.

Richard

 

I knew it was something like that, thanks.