Laravel get SUM of column with reference to other column

Question

I have a table with name transaction (Model: Transaction). Here is my table structure:

Type	Quantity
+	10
-	4
+	15
-	15

Here I want sum of total quantity, where all the - should be subtracted and all the +should be added. How can I get ?

I have added all + (this is purchase) and subtracted - (this is sale) with 2 different queries this way: Transation::sum('Quantity')->where('Type','+') - Transation::sum('Quantity')->where('Type','-').

Is there any better and single query than multiple queries ?

Why a type column, and not simply have negative quantity values? ... then only a sum of the quantity column would be needed, which is only 1 query. — Paul T., Jun 28 '23 at 12:13
not sure if it'll work, but try this way - `$totalQuantity = Transaction::sum( \DB::raw("CASE WHEN Type = '+' THEN Quantity ELSE -Quantity END") );` — ericmp, Jun 28 '23 at 12:28
@PaulT. That is correct. But I cannot do that for some reason. — Learner, Jun 28 '23 at 13:07

score 1 · Answer 1 · answered Jun 28 '23 at 13:10

You can perform the calculation directly on your query using selectRaw and do sql syntax.

return Transaction::selectRaw("(
        SUM(CASE WHEN Type = '+' THEN Quantity END) - 
        SUM(CASE WHEN Type = '-' THEN Quantity END) 
    ) as total")
    ->get();

or group the table by Type then perform the calculation in laravel collection

$data = Transaction::select('Type as operator')
    ->selectRaw('sum(Quantity) as total')
    ->groupBy('operator')
    ->get();

// here transform the collection as a signed integer value like [-10, +10] then add them all by calling sum
return $data->map( fn( $i) => (int) ($i->operator . $i->total) )->sum();

score 1 · Answer 2 · answered Jun 28 '23 at 21:22

Yes you can, you use Laravel eloquent select Raw and that will allow you to use case just like a switch statement, an example of this

$totalQuantity = DB::select(DB::raw('SELECT SUM(CASE WHEN Type = \'+\' THEN Quantity ELSE -Quantity END) AS total_quantity FROM transactions'))[0]->total_quantity;

score 0 · Answer 3 · answered Jun 28 '23 at 13:17

This can be done by accessing your data with your eloquent model. Then you can iterate through each and check the value by accessing the properties and operating based on that.

Just a note on using Model::all(), if you have many elements you should use chunk instead. all() will load all the data into memory, which can be exceeded with too many entries. You can read more about that here: Select all from table with Laravel and Eloquent

$rows = Transation::all();
  
$total = 0;
   
$rows->each(function($row)
{
    switch($row->Type)
    {
        case '+':
            $total += $row->Quantity;
            break;
        case '-';
            $total -= $row->Quantity;
            break;
    }   
}

User863 · Answer 4 · 2023-06-30T08:53:07.980

0

Try calculating in MySQL itself using Laravel's selectRaw

Transaction::selectRaw('SUM(CONCAT(type, quantity)) AS total')->first()->total ?? 0

edited Jun 30 '23 at 08:53

answered Jun 28 '23 at 13:50

User863

19,346
2
17
41

Laravel get SUM of column with reference to other column

4 Answers4