Group By Year & Month of a timestamp field

I want to do a count of users grouped by the month & year of a timestamp field. I’ve been playing around with the query below, but haven’t yet figured out the GROUP BY portion. How do I just group on the Year and Month of the timestamp field (payment_datetime) which is in the format of 2009-12-11 14:04:37?


SELECT COUNT( DISTINCT `hocust_id` ) , CONCAT( YEAR( `payment_datetime` ) , '-', MONTH( `payment_datetime` ) ) AS thedate
FROM `ht_op_payment`
[B][COLOR="Red"]GROUP BY DATE( YEAR & MONTH )[/COLOR][/B]

Try this (didn’t test it):


SELECT 
    COUNT( DISTINCT hocust_id ) 
  , CONCAT( YEAR( payment_datetime ) , '-', MONTH( payment_datetime ) ) AS thedate
FROM ht_op_payment
GROUP BY CONCAT( YEAR( payment_datetime ) , '-', MONTH( payment_datetime ) ) 

geez, its always the obvious. don’t know why I didn’t think of that. it appears to work. i’ll have to do some testing. thanks
Guido.

actually, even though you have this in the SELECT clause –


CONCAT( YEAR( payment_datetime ) , '-', MONTH( payment_datetime ) )

you can simply put this in the GROUP BY clause –


GROUP BY YEAR( payment_datetime ) , MONTH( payment_datetime )