Laravel Eloquent Sum of Multiplied Columns

Laravel Eloquent sum of multiplied columns

Try use this

$amount = Transaction::select(DB::raw('sum(jumlah * harga) as total'))->get();

How to get Total Sum of one column after multipling with other column values

You can either multiply and sum the values using an SQL query (documentation):

$total = DB::table('stock')->selectRaw('purchase * stock AS total')->sum('total');

Or you can use a reducer on your collection (documentation):

$total = Stock::all()->reduce(function ($total, $item) {
    return $total + ($item->purchase * $item->stock);
});

Laravel 5.7 - multiply two columns and add to final sum

With your edit I was able to solve the problem that you have, but in your edit you use couple of things for which I don't have data, such as the $team, first_name and last_name of the students. But anyway, here is a solution for your problem, you have to use subqueries in order to solve this:

$teamStudents = DB::table('students')->orderBy('name', 'ASC')
    ->leftJoin(DB::raw('(select SUM(products.money * products.quantity) AS products_total, student_id from products group by student_id) products'), 'students.id', '=', 'products.student_id')
    ->leftJoin(DB::raw('(select sum(payments.money) as payments_total, student_id from payments group by student_id) payments'), 'students.id', '=',
        'payments.student_id')
    ->select('students.name', 'payments.payments_total', 'products.products_total', 'students.id AS id')
    ->groupBy('students.id')
    ->get();

I am not sure if technically I will be correct, but the problem is because you use multiple joins, so that's why the results are doubled, if you don't use subqueries.

Laravel 5 Eloquent sum of multiplied columns for mongo DB

I believe aggregation operators like sum expect exact column name as a parameter. You can try to project the multiplication first, then sum the result:

DB::connection($this->MongoSchemaName)
    ->collection($this->InvoicesTable)
    ->where('ContactID', (int)$customer->ContactID)
    ->project([
        'ContactID' => 1, 
        'TotalInBaseCurrency' => ['$multiply' => ['$Total', '$CurrencyRate']]
    ])
    ->sum('TotalInBaseCurrency')

or use aggregation directly:

DB::connection($this->MongoSchemaName)
    ->collection($this->InvoicesTable)
    ->raw(function($collection) use ($customer){
        return $collection->aggregate([
            ['$match' => [
                    'ContactID' => (int)$customer->ContactID,
                    'Type' => 'PAYMENT'
                ]
            ],
            ['$group' => [
                '_id' => '$ContactID',
                'TotalInBaseCurrency' => [
                        '$sum' => ['$multiply' => ['$Total', '$CurrencyRate']]
                    ]
                ]
            ]
        ]);
    })

How to multiply and group using laravel eloquent with relation

This can be solved by a join with SUM(), something like below (untested):

Budget::leftJoin('items', 'budgets.item_code', '=', 'items.item_code')
    ->addSelect('subgroup')
    ->addSelect('delivery_plan')
    ->addselect(\DB::raw('SUM(qty * price) as total'))
    ->groupBy('subgroup', 'delivery_plan')
    ->get();

---

Related Topics