title: GROUP and COUNT() ages in CakePHP
body: <p>I'm trying to group by date of birth and count based on the results, using  CakePHP. Here's my query.</p>
  
  <pre><code>$data = $this-&gt;User-&gt;find('all', array(
      'fields' =&gt; array(
          "DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') &lt; DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
          'COUNT(id)'
      ),
      'group' =&gt; 'age'
  ));
  </code></pre>
  
  <p>So far, so good. The field <code>User.dob</code> is date of birth, it's a <code>DATETIME</code> field.</p>
  
  <p>Thing is, it returns something like this:</p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [age] =&gt; 9
                      [COUNT(id)] =&gt; 1
                  )
  
          )
  
      [1] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [age] =&gt; 10
                      [COUNT(id)] =&gt; 1
                  )
  
          )
  
      [2] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [age] =&gt; 11
                      [COUNT(id)] =&gt; 1
                  )
  
          )
  
      [3] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [age] =&gt; 12
                      [COUNT(id)] =&gt; 8
                  )
  
          )
  
      [4] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [age] =&gt; 13
                      [COUNT(id)] =&gt; 1
                  )
  
          )
  </code></pre>
  
  <p>Surely there must be a better way.</p>
  
  <p>And I can't even filter it. This code throws error. Unknown column 'age'</p>
  
  <pre><code>$data = $this-&gt;User-&gt;find('all', array(
      'conditions' =&gt; array('age &gt;' =&gt; 20),
      'fields' =&gt; array(
          "DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') &lt; DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
          'COUNT(id)'
      ),
      'group' =&gt; 'age'
  ));
  </code></pre>
  
  <p>By the way, these are the queries.</p>
  
  <pre><code>SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') &lt; DATE_FORMAT(User.dob, '00-%m-%d')) AS age, COUNT(id) FROM `users` AS `User` WHERE 1 = 1 GROUP BY age 
  </code></pre>
  
  <p>(The age calculation routine was found in <a href="http://ma.tt/2003/12/calculate-age-in-mysql/" rel="noreferrer">matt's blog</a>.)</p>