How to Calculate the Sum of Multiple Columns Using Eloquent

How to Calculate the Sum of Multiple Columns Using Eloquent

in this tutorials i will show you How to Calculate the Sum of Multiple Columns Using Eloquent. Laravel offers built-in support for aggregating column values through the ‘sum‘ method, allowing you to calculate the total of a column’s values. You Can learn How to Calculate the Average of a Column Using Eloquent

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! How to Calculate the Sum of Multiple Columns Using Eloquent

How to Calculate the Sum of Multiple Columns Using Eloquent

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]);

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:

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

Leave a Reply