I just did something similar, using only CakePHP (no direct queries). It works in CakePHP 2, haven't tested in 1.x.
The code for your example would be something like this:
$params = array(
'recursive' => -1,
'fields' => array('id', 'MONTH(created)')
'group' => array('YEAR(created)', 'MONTH(created)')
$numberOfPosts = $this->Model->find('count', $params);