Skip to content

Yii2 How to translate SUM SQL function to Query Builder?

I have this simple SQL query:

SELECT product_name, SUM (product_amount) 
FROM orders
GROUP BY product_name;

It will show a list with product names and their amounts. Like this example:

SQL query result

I want to translate it to Yii2 Query Builder. I don’t know how to use the SUM function. I tried this but it didn’t work:

            Orders::tableName() . ".product_name",
            (new yiidbQuery())->sum(Orders::tableName() . ".product_amount")
            Orders::tableName() . '.product_name',
            Orders::tableName() . '.product_amount'



You need to use yiidbExpression while selecting as you are trying to call the SQL SUM() function and you need not to quote the function while selecting.

Expression represents a DB expression that does not need escaping or quoting. Expression objects are mainly created for passing raw SQL expressions to methods of yiidbQuery, yiidbActiveQuery and related classes.

Change your code to

    ->select(['product_name', new yiidbExpression('SUM(product_amount)')])
User contributions licensed under: CC BY-SA
1 People found this is helpful