Yii2 : How to write distinct SQL query?

Question

I want to implement following SQL queries in Yii 2 but with no success.

This should give total number of unique company names:

SELECT count(DISTINCT(company_name)) FROM clients

And this should display company_name with client code and id(PK):

SELECT (DISTINCT(company_name,client_code)) FROM clients

How to achieve this?

You want to return a table that has two columns: company_name and client_code; unique values only? The union will create a single column of both names and ID's combined. I suspect that's not what you want. Couldn't you just: `select distinct company_name, client_code from clients`? — Alex Woolford, Jul 11 '15 at 20:46
First I want the count of all Unique **comapny_name** and after that in second query I want to list of all unique **company_name** and **client_code** — JKLM, Jul 11 '15 at 20:53
Does the first query work (i.e. `count(distinct(company_name))`)? The syntax looks good to me. — Alex Woolford, Jul 11 '15 at 20:57
got the count of unique company name but dont know how to list those.. — JKLM, Jul 11 '15 at 20:58
Can you try `select distinct company_name, client_code from clients`? — Alex Woolford, Jul 11 '15 at 20:59
did you try: `$data = (new yii\db\Query())->select(['company_name', 'client_code'])->from('clients')->distinct()->all();` ? — Tony, Jul 12 '15 at 07:26
Please post your findings as a new answer. Providing (and selecting) an answer to your own question makes it easier for people with the same problem to find a solution. — tarleb, Jul 17 '15 at 18:28

score 30 · Answer 1 · edited Aug 26 '18 at 11:24

Try this:

$total = YourModel::find()->select('company_name')->distinct()->count();

In Search Model:

public function search($params)
{
    $query = YourModel::find()->select('company_name')->distinct();
    // or
    $query = YourModel::find()->select(['company_name', 'client_code'])->distinct();

    $query->orderBy('id desc');

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
    // ...
}

score 14 · Accepted Answer · edited Aug 26 '18 at 11:27

Answering my own question I got following working solutions:

Got the count for unique company_name:

$my = (new yii\db\Query())
    ->select(['company_name',])
    ->from('create_client')
    ->distinct()
    ->count();
echo $my;

List of distinct company_name and client_code:

$query = new yii\db\Query();
$data = $query->select(['company_name','client_code'])
    ->from('create_client')
    ->distinct()
    ->all();
if ($data) {
    foreach ($data as $row) {
        echo 'company_name: ' . $row['company_name'] . ' client_code: ' . $row['client_code'] . '<br>';
    }
}

scaisEdge · Answer 3 · 2018-08-26T06:21:44.633

2

I hope this sample is useful for you

 $names = Yii::$app->db->createCommand('SELECT  count(DISTINCT(company_name)) as name FROM clients')
    ->queryAll();

for access the the data

foreach ($names as $name){
    echo $name['name'];
}

edited Aug 26 '18 at 06:21

answered Jul 12 '15 at 07:22

scaisEdge

124,973
10
73
87

I have post a new answer. i hope this is correct for you. – scaisEdge Jul 12 '15 at 07:23
I got the solution, updated the question Thanks for helping .. :) – JKLM Jul 12 '15 at 09:26
Change the way to access the db object, It is `db` instead of `$db`, related https://www.yiiframework.com/doc/guide/2.0/en/db-dao#creating-db-connections (Creating DB Connections) – Ale Aug 25 '18 at 20:07

score 1 · Answer 4 · answered Sep 05 '16 at 23:43

1

All worked fine

return Clients::find()->count('DISTINCT(company_name)');

answered Sep 05 '16 at 23:43

user2382679

119
1
2

Yii2 : How to write distinct SQL query?

4 Answers4

Linked