Question 195957 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel get SUM of column with reference to other column

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 ?

Tags:

3

Answers


  1. 0

    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();
    Login or Signup to reply.
  2. 0

    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;
    }   
}
    Login or Signup to reply.
  3. 0

    Try calculating in MySQL itself using Laravel’s selectRaw

    AppModelsTransaction::selectRaw('SUM(CONCAT(type, quantity)) as total')->first()->total ?? 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search