How to implement such a query in Yii2?

Question

SQL

Hello! There is such a, relatively, not tricky request:

SELECT A.agency_id, A.agency_name, SUM( B.amount ) AS summ
FROM agency AS A
LEFT OUTER JOIN billing B ON B.date > '2006-05-01' AND B.date < '2006-07-20' AND B.agency_id = A.agency_id
GROUP BY A.agency_name

And the tables:
Agency:

Billing:

You need to get this idea:

I.e. for each agency, calculate the amount for the specified period.
There is a script on SQL, here on Yii2 (yii\db\Query), so far I have only come to this:

query = (new Query())
    ->select(['a.agency_name', 'b.amount'])
    ->from(['agency a'])
    ->leftJoin('billing b', "b.agency_id = a.agency_id AND b.date > '2006-05-01' AND b.date < '2007-07-20' ")
    ->groupBy('a.agency_name');

Add ->sum('b.amount') here, swears,
"The "query" property must be an instance of a class that implements the QueryInterface eg yii\db\Query or its subclasses".
Please tell me how to correctly implement this query in Yii2 (yii\db\Query).
Thanks in advance!

Answer 1

So far I've decided this:

$query = Yii::$app->db->createCommand("
                                SELECT A.agency_id, A.agency_name, SUM( B.amount ) AS summ
                                FROM agency AS A
                                LEFT OUTER JOIN billing B ON B.date > '2006-05-01'
                                    AND B.date < '2006-07-20'
                                    AND B.agency_id = A.agency_id
                                GROUP BY A.agency_name");
        $result= $query->queryAll();

But there was another question:
I pass the request to the data provider:

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

And I get the same error: "The "query" property must be an instance of a class that implements the QueryInterface eg yii\db\Query or its subclasses".

Answer 2

For a start GROUP BY A.agency_name
, replace with
GROUP BY A.agency_id, A.agency_name