Using DISTINCT inside a COUNT with CakePHP 3

Question

I'm new with CakePHP 3 and I want to know how I can use a DISTINCT inside a COUNT such as the following SQL query.

SELECT `column_one`, COUNT(DISTINCT `column_two`) FROM `table` GROUP BY `column_one`

arilia · Accepted Answer · 2015-10-20 17:27:17Z

6

you can simply do

$query = $table->find()
->select([
    'column_one',
    'count' => "COUNT(DISTINCT `column_two`)"
])
->group(['column_one']);

but if you want to use cakephp sql function you can do

...
'count' => $query->func()->count('DISTINCT `column_two`')
....

answered Oct 20, 2015 at 17:27

arilia

9,3982 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Birney Over a year ago

It would be a bit clearer if you included a complete solution using the native CakePHP count() function; it's not immediately obvious from your solution that $query needs to be instantiated separately. Like so: $query = $table->find('all'); $query = $query->select([ 'column_one', 'count' => $query->func()->count('DISTINCT column_two')]);

Collectives™ on Stack Overflow

Using DISTINCT inside a COUNT with CakePHP 3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related