CakePHP and SQL Server 2008, Group By not working

Question

Whenever I do a:

$this->Job->find('all', array(
        'group' => array('Job.some_field'),
        'recursive' => -1
    ));

I get a :

SQL Error: Column 'jobs.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

With MySQL it works fine but with SQL Server 2008 it seems that group by doesn't work anymore. How do I fix this? Thanks in advance SQL gurus

Answer 1

The query is translated into something like this:

SELECT  *
FROM    Job
GROUP BY
        some_field

This is not a valid query according to SQL standards, however, it works in MySQL because of MySQL's GROUP BY extensions.

You need to leave only grouped columns or aggregates in the SELECT clause:

SELECT  some_field, COUNT(*)
FROM    Job
GROUP BY
        some_field

with something like this:

$this->Job->find('all', array(
        'fields' => array('Job.some_field', 'COUNT(*)'),
        'group' => array('Job.some_field'),
        'recursive' => -1
    ));