How to count and group by in yii2

In Yii2, you can use the count() and groupBy() methods in the query builder to count and group the results of a query, respectively.

Here is an example of how to use these methods to count the number of users grouped by their role:

$users = (new \yii\db\Query())
    ->select(['role', 'COUNT(*) as count'])
    ->from('user')
    ->groupBy(['role'])
    ->all();

In this example, count(*) is used to count the number of rows for each role, and the results are grouped by the role column. The query returns an array of arrays, each containing the role and count for that group.

You can also use the createCommand() method to execute the sql statement and get the count of grouped data

$count = Yii::$app->db->createCommand("SELECT role, COUNT(*) as count FROM user GROUP BY role")->queryAll();

You can also use count() method to get the count of rows in a table

$count = (new \yii\db\Query())->from('user')->count();

Both of the above examples will return the number of rows in the 'user' table.