Tarun Bhardwaj Tarun Bhardwaj - 3 months ago 27
MySQL Question

Cakephp 2.x find query having DISTINCT and COUNT together on same field

User
table structure.

+------+-------+
| id | data |
+------+-------+
| 1 | a |
+------+-------+
| 1 | b |
+------+-------+
| 2 | c |
+------+-------+


Desired result.

+------+-------+
| id | count |
+------+-------+
| 1 | 2 |
+------+-------+
| 2 | 1 |
+------+-------+


I tried DISTINCT and COUNT in many ways but still not getting desired result.

What i am trying

$this->User->find('all',array(
'conditions'=> array(),
'fields' => array(
'DISTINCT(User.id)',
'COUNT(DISTINCT User.id) as count'
)
)
);

Answer

Try a group by clause

$this->User->find('all',
    array(
        'fields' => array(
            'User.id',
            'COUNT(User.id) as count'
        ),
        'group' => 'User.id'
    )
);