Laravel Sum Query with Where Condition Example

Laravel Sum Query – i often need to get sum of total visitors, amount, salary etc in PHP laravel. get the sum of two different columns using Laravel query builder.

Laravel Sum Query with Where Condition

I can also get the total sum of column using mysql SUM() bellow Laravel Select with Sum Query Example.

Example 1: using having()

Laravel Controller Code:

<?php
  
namespace App\Http\Controllers;
  
use App\Models\Member;
use DB;
  
class PakaController extends Controller
{
    /**
     * Write code on Method
     * created by Pakainfo.com
     * @return response()
     */
    public function index()
    {
        $members = MemberSalary::select("*", DB::raw('SUM(rank) as total'))
                    ->groupBy("member_id")
                    ->having('total', '>', 50)
                    ->get();
  
        dd($members);
    }
}

Result

Array
(
    [0] => Array
        (
            [id] => 1
            [member_id] => 1
            [rank] => 56
            [salary_date] => 2021-08-04
            [is_active] => 1
            [created_at] => 
            [updated_at] => 
            [total] => 56
        )
    [1] => Array
        (
            [id] => 2
            [member_id] => 2
            [rank] => 45
            [salary_date] => 2021-09-07
            [is_active] => 0
            [created_at] => 
            [updated_at] => 
            [total] => 55
        )
    [2] => Array
        (
            [id] => 5
            [member_id] => 5
            [rank] => 66
            [salary_date] => 2021-01-04
            [is_active] => 1
            [created_at] => 
            [updated_at] => 
            [total] => 66
        )
)

Don’t miss : Sql Concatenate Two Columns in Laravel

Example 2: using havingRaw()

Laravel Controller Code:

<?php
  
namespace App\Http\Controllers;
  
use App\Models\Member;
use DB;
  
class PakaController extends Controller
{
    /**
     * Write code on Method
     * created by Pakainfo.com
     * @return response()
     */
    public function index()
    {
        $members = MemberSalary::select("*", DB::raw('SUM(rank) as total'))
                    ->groupBy("member_id")
                    ->havingRaw('total > 50')
                    ->get();
  
        dd($members);
    }
}

Result

Array
(
    [0] => Array
        (
            [id] => 1
            [member_id] => 1
            [rank] => 56
            [salary_date] => 2021-08-04
            [is_active] => 1
            [created_at] => 
            [updated_at] => 
            [total] => 56
        )
    [1] => Array
        (
            [id] => 2
            [member_id] => 2
            [rank] => 45
            [salary_date] => 2021-09-07
            [is_active] => 0
            [created_at] => 
            [updated_at] => 
            [total] => 55
        )
    [2] => Array
        (
            [id] => 5
            [member_id] => 5
            [rank] => 66
            [salary_date] => 2021-01-04
            [is_active] => 1
            [created_at] => 
            [updated_at] => 
            [total] => 66
        )
)

laravel sum query with where condition

$results = DB::table('historys')
    ->join('remarks', 'historys.category_id', '=', 'remarks.id')
    ->where('remarks.kind', '=', 1)
    ->sum('historys.amount');

laravel sum with condition

Member::whereHas('attendance')
            ->withSum(['attendance' => function ($query) use ($start, $end){
                $query->whereBetween('date', [$start, $end])
                    ->where('status', 2);
            }], 'hours')
            ->get();

laravel sum multiple columns

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

laravel join and sum eloquent

laravel join sum

$Members = Member::select('Members*', 'stats.*', DB::raw('SUM(stats.revenue) As revenue'))
         ->leftJoin('stats', 'stats.Member_id', '=', 'Members.id')
         ->where('stats.date', Carbon::today()->toDateString())
         ->get();

select sum laravel

Sometime for such queries you need to disable the strict check So inside config/database.php and inside mysql, Set ‘strict’ => false,

Also Read This ๐Ÿ‘‰   How to get the current page name using PHP?

->select('Member_id', DB::raw('SUM(points) as total_points'))

Laravel Eloquent withSum() and withCount() Tutorial

laravel withsum condition

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Category extends Model
{
    use HasFactory;
    /**
     * Get the comments for the blog post.
     */
    public function Assets()
    {
        return $this->hasMany(Asset::class);
    }
}

Asset Model:

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Asset extends Model
{
    use HasFactory;
    protected $fillable = [
        'name', 'price'
    ];
}

Laravel relationship with withSum() Example:

<?php
namespace App\Http\Controllers;
use App\Models\Category;
class MyController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $remarks = Category::select("id", "name")
                        ->withSum('Assets', 'price')
                        ->get()
                        ->toArray();
        dd($remarks);
    }
}

laravel query builder sum group by

$data = DB::table("click")
        ->select(DB::raw("SUM(numberofclick) as count"))
        ->orderBy("created_at")
        ->groupBy(DB::raw("year(created_at)"))
        ->get();
print_r($data);

laravel sum two columns

Priority::sum(DB::raw('priority + priority'));

laravel where sum

$sum = Model::where('status', 'paid')->sum('sum_field');

sql select sum group by id laravel join
laravel left join sum

$Members = Member::select('Members*', 'stats.*', DB::raw('SUM(stats.revenue) As revenue'))
         ->leftJoin('stats', 'stats.Member_id', '=', 'Members.id')
         ->where('stats.date', Carbon::today()->toDateString())
         ->get();

laravel multiple sum

$q = DB::table('events')
       ->join('attendees', 'events.id', '=', 'attendees.event_id')
       ->sum('total_raised')
       ->sum('total_hours');

Laravel Sum Query with Where Condition Example

Example 1: using having() : laravel having sum

<?php
  
namespace App\Http\Controllers;
  
use App\Models\Member;
use DB;
  
class ITSController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $Members = MemberPayment::select("*", DB::raw('SUM(amount) as total'))
                    ->groupBy("Member_id")
                    ->having('total', '>', 50)
                    ->get();
  
        dd($Members);
    }
}

Example 2: using havingRaw() – laravel collection sum with condition

<?php
  
namespace App\Http\Controllers;
  
use App\Models\Member;
use DB;
  
class ITSController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $Members = MemberPayment::select("*", DB::raw('SUM(amount) as total'))
                    ->groupBy("Member_id")
                    ->havingRaw('total > 50')
                    ->get();
  
        dd($Members);
    }
}

sum with two columns in laravel

laravel sum of two columns OR sum two columns in eloquent

\App\Models\YourModel::sum(DB::raw('field1 + field2'));

laravel sum 2 columns

$result = DB::table('daily')
  ->select([
		'excavatorId',
		DB::raw("SUM(times_loaded) as total_times_loaded"),
		DB::raw("SUM(litres) as total_liters"),
	])
  ->groupBy('excavatorId')
  ->where('date', $request->input('date'))
  ->get();

Laravel Eloquent Sum Multiple Columns Example

sum multiple columns laravel

<?php
  
namespace App\Http\Controllers;
  
use App\Models\Asset;
use DB;
  
class SignaturePadController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $Assets = Asset::select(
                            "id", 
                            "name", 
                            DB::raw("SUM(sell) as total_sell"),
                            DB::raw("SUM(stock) as total_stock")
                        )
                    ->groupBy("category_id")
                    ->get();
          
        dd($Assets->toArray());
    }
}

Result : laravel sum query multiple columns

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Silver
            [total_sell] => 40
            [total_stock] => 139
        )
    [1] => Array
        (
            [id] => 49
            [name] => Apple
            [total_sell] => 
            [total_stock] => 3
        )
    [2] => Array
        (
            [id] => 52
            [name] => Dell
            [total_sell] => 
            [total_stock] => 2
        )
)

Laravel db raw with sum of column example

sum raw laravel

Also Read This ๐Ÿ‘‰   mysql return id after insert - 2 ways to insert and return id mysql

$data = DB::table("Members")
	    ->select(DB::raw("SUM(Members_address.id) as total_address"))
	    ->leftjoin("Members_address","Members_address.Member_id","=","Members.id")
	    ->groupBy("Members.id")
	    ->get();
print_r($data);

laravel aggregate sum

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Member;
  
class BookController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $Book = Book::sum('like_count');
       \\or
       DB::table('Books')->sum('like_count');
   }
}

select sum(`like_count`) from `Books`

sum function in laravel

MyModel::where('Member_id', $_some_id)->sum('amount')

Laravel Eloquent Group By Year with Sum Example

laravel group by sum

<?php
   
namespace App\Http\Controllers;
  
use App\Models\Visitor;
use DB;
  
class DemoController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $visitors = Visitor::select(
                            "id" ,
                            DB::raw("(sum(click)) as total_click"),
                            DB::raw("(DATE_FORMAT(created_at, '%Y')) as my_year")
                            )
                            ->orderBy('created_at')
                            ->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y')"))
                            ->get();
  
        dd($visitors);
    }
}

Result

Array
(
    [0] => Array
        (
            [id] => 1
            [total_click] => 29
            [my_year] => 2020
        )
    [1] => Array
        (
            [id] => 3
            [total_click] => 101
            [my_year] => 2021
        )
)

laravel query sum group by

$data = DB::table("click")
	    ->select(DB::raw("SUM(numberofclick) as count"))
	    ->orderBy("created_at")
	    ->groupBy(DB::raw("year(created_at)"))
	    ->get();
print_r($data);

Laravel Eloquent Sum Multiple Columns Example

eloquent sum multiple columns

<?php
  
namespace App\Http\Controllers;
  
use App\Models\Asset;
use DB;
  
class SignaturePadController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $Assets = Asset::select(
                            "id", 
                            "name", 
                            DB::raw("SUM(sell) as total_sell"),
                            DB::raw("SUM(stock) as total_stock")
                        )
                    ->groupBy("category_id")
                    ->get();
          
        dd($Assets->toArray());
    }
}

Result

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Silver
            [total_sell] => 40
            [total_stock] => 139
        )
    [1] => Array
        (
            [id] => 49
            [name] => Apple
            [total_sell] => 
            [total_stock] => 3
        )
    [2] => Array
        (
            [id] => 52
            [name] => Dell
            [total_sell] => 
            [total_stock] => 2
        )
)

select sum laravel eloquent

$data=Model::where('Member_id','=',$id)->sum('movement');
return $data;

laravel db sum

$data = DB::table("click")->sum('numberofclick');
print_r($data);

laravel sum array column

$sumArray = array();

foreach ($myArray as $k=>$subArray) {
  foreach ($subArray as $id=>$value) {
    $sumArray[$id]+=$value;
  }
}

print_r($sumArray);

sum in laravel controller

laravel eloquent sum column

Asset::where('id',$id)->sum('quantity');

eloquent sum group by

$docs = Document::groupBy('Members_editor_id')
   ->selectRaw('sum(no_of_pages) as sum, Members_editor_id')
   ->get(); //this return collection 

//In Blade
@foreach($docs as $doc)
	{{$doc->Members_editor_id}} >-------< {{$doc->sum}}
@endforeach

laravel collection group by sum

Document::Where('some_condition',true)
   ->select([DB::raw("SUM(debit) as total_debit"), DB::raw("SUM(credit) as total_credit")])
   ->groupBy('id')
   ->get()

laravel sum array values

$collection = collect([9, 20, 34]);
 
$piped = $collection->pipe(function ($collection) {
    return $collection->sum();
});
 

I hope you get an idea about laravel sum query.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, donโ€™t forget to share.