need help with UNIX_TIMESTAMP() with GROUP BY

[tjhilder]

Hi,

I have this query:
[code]$query = "SELECT UNIX_TIMESTAMP(date) AS udate, blog.*, COUNT(blog_id) AS blog_count FROM blog GROUP BY date ORDER BY date DESC";[/code]

this statement is meant to create a list of archives like this...

March 2006 (7)
February 2006 (3)
January 2006 (2)

etc, but since the date is in datetime format --> 0000-00-00 00:00:00 it can't GROUP BY properly, I used to use another field that would display 'may 2006' etc in the news marked as that month (which worked fine) but I want to be able to group them using 'date'.

I'm not sure how to go about this, so that it uses the first 0000-00.. etc as a way of grouping, but I'm not sure if this is possible and if it is, how I do it.

could someone please help me?

thanks in advance.

--
Terry

[fenway]

You may want to try grouping by "EXTRACT( YEAR_MONTH FROM date )"; obviously, you could use a pretty DATE_FORMAT() call for the output itself. Incidentally, date is a reserved keyword, so be careful.

Hope that helps.

[tjhilder]

thanks alot, I managed to get it working with DATE_FORMAT().

thanks again! [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]