Laravel offers built-in support for aggregating column values through the ‘sum‘ method, allowing you to calculate the total of a column’s values.

The main caveat here is that Eloquent can only sum one column at a time. To sum 2 columns we either call sum() twice and add them or we use DB::raw to explicitly make a SUM of both columns:

$product = 'PS5'

// Calculating `online_sales + in_store_sales` using ->sum()
$eloquentSum = Sale::where('product_name', $product)->sum('online_sales')
               + Sale::where('product_name', $product)->sum('in_store_sales');

// Calculating `online_sales + in_store_sales` using DB::raw('SUM(..)'
$rawSum = Sale::where('product_name', $product)
    ->select(DB::raw('SUM(online_sales + in_store_sales) as total_sales'))
    ->value('total_sales');

In this post we’ll make a simple application with a migration and model, add some test records and finally demonstrate how to calculate the sum of 2 columns to print online_sales + in_store_sales of a specific product. I will include an example for both the method that uses Eloquent’s sum() as well as the method that uses DB:raw('SUM(..)).

Let’s get started!

Step 1: Create a Laravel Project

Begin by creating a new Laravel project using the following commands in your terminal:

composer create-project laravel/laravel sum-columns-example
cd sum-columns-example

Step 2: Create Migrations

Generate a migration for a table sales:

php artisan make:migration create_sales_table --create=sales

Step 3: Add Migration Code

In the generated migration add:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('sales', function (Blueprint $table) {
            $table->id();
            $table->string('product_name');
            $table->integer('online_sales');
            $table->integer('in_store_sales');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('sales');
    }
};

Step 4: Run the migration:

Run the migration using artisan:

php artisan migrate

Step 5: Create a Model

Now let’s use artisan to generate a model for “Sales” by running:

php artisan make:model Sale

Step 6: Add Model Code

Add the following code for the model “Sales” to define its $fillable columns:

app/Models/Sale.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Sale extends Model
{
    protected $fillable = [
        'product_name',
        'online_sales',
        'in_store_sales',
    ];
}

Step 7: Insert Test Data:

Let’s insert some test data for the “Sales” model using Laravel Tinker. First run:

php artisan tinker

Then in the Tinker Shell copy & paste the following PHP Code:

use App\Models\Sale;

$sale = Sale::create(['product_name' => 'PS5', 'online_sales' => 120, 'in_store_sales' => 15]);
$sale = Sale::create(['product_name' => 'Xbox Series X', 'online_sales' => 133, 'in_store_sales' => 10]);

Our table now contains the following data:

Screenshot of Our Table `sales` After Filling it With Testdata

Step 8: Add a Route

To test calculating the sum of online_sales + in_store_sales open your route file at routes/web.php and add the following code:

routes/web.php

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;
use App\Models\Sale;

Route::get('/test', function () {

    $product = 'PS5'; // Change this to the desired product

    // Calculating `online_sales + in_store_sales` using ->sum()
    $eloquentSum = Sale::where('product_name', $product)->sum('online_sales')
                   + Sale::where('product_name', $product)->sum('in_store_sales');
    echo "Total sales calculated with Eloquent sum()";
    dump($eloquentSum);

    // Calculating `online_sales + in_store_sales` using DB::raw('SUM(..)')
    $rawSum = Sale::where('product_name', $product)
    ->select(DB::raw('SUM(online_sales + in_store_sales) as total_sales'))
    ->value('total_sales');
    echo "Total sales calculated with DB::raw('SUM(..)'";
    dump($rawSum);
});

Step 9: Test the application

Start the application by running:

php artisan serve

Then open our test page at: http://127.0.0.1:8000/test

You should see the following output:

Our Testpage Showing Sum of Sales Using the Two Method Discussed

That’s it! You’ve just learned two methods to calculate the sum of multiple column values.

Note that the method that calls sum() twice requires running 2 queries while the DB:raw('SUM(..)') requires only one. In general calling sum() twice will be slower but more readable while using DB:raw enables us to optimize the code while sacrificing a bit of readability.

-- Calling ->sum() + ->sum() runs 2 queries:
select sum(`online_sales`) as aggregate from `sales` where `product_name` = 'PS5'
select sum(`in_store_sales`) as aggregate from `sales` where `product_name` = 'PS5'

-- Using DB::raw('SUM(online_sales + in_store_sales) runs only 1 query:
select SUM(online_sales + in_store_sales) as total_sales from `sales` where `product_name` = 'PS5' limit 1

Conclusion

Congratulations! You’ve successfully explored two methods to calculate the sum of multiple column values in Laravel: on the one hand using Eloquent’s sum() method and the DB::raw approach on the other.

Keep in mind that the sum() method, when called twice, may be less optimized but offers better readability, while the DB::raw('SUM(..)') method optimizes the code at the cost of a bit of readability. Choose the approach that best fits your preferences and project requirements.

Now armed with this knowledge, go ahead and implement sum calculations in your own applications. Happy coding!

References

Eloquent: Retrieving Aggregates (Laravel Documentation)

Database Migrations (Laravel Documentation)

This entry is part 2 of 4 in the series Aggregating With Laravel Eloquent

How to Calculate the Average of a Column Using Eloquent

How to Calculate the Sum of Multiple Columns Using Eloquent
How to Calculate the Sum of a Relation Column in Eloquent
How to Get the Max Value of a Column Using Eloquent

How to Calculate the Sum of Multiple Columns Using Eloquent