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:

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:

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,

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

Laravel Eloquent withSum() and withCount() Tutorial

laravel withsum condition

hasMany(Asset::class);
    }
}

Asset Model:

Laravel relationship with withSum() Example:
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
groupBy("Member_id")
                    ->having('total', '>', 50)
                    ->get();
  
        dd($Members);
    }
}

Example 2: using havingRaw() - laravel collection sum with condition
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
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
$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
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
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
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();
});
 

How To Select with Sum Query In Laravel?
In Laravel, you can use the DB facade to execute raw SQL queries and retrieve the result using various methods like select, pluck, and get. To execute a sum query, you can use the sum method, which is available on the DB facade.

Here's an example of how to execute a sum query in Laravel:
$total = DB::table('orders')
                ->where('user_id', '=', $user_id)
                ->sum('total_amount');


In this example, we are selecting the total amount of orders made by a user specified by $user_id. We use the where method to filter the results by the user ID and the sum method to calculate the sum of the total_amount column.
You can also use the select method to select other columns along with the sum result:
$total = DB::table('orders')
	->select(DB::raw('SUM(total_amount) as total'))
	->where('user_id', '=', $user_id)
	->first();

In this example, we are selecting the sum of the total_amount column and giving it an alias of total. We use the first method to retrieve the first result, which will contain the sum result in the total property.
You can also use the groupBy method to group the results by a particular column:
$totals = DB::table('orders')
	->select('user_id', DB::raw('SUM(total_amount) as total'))
	->groupBy('user_id')
	->get();

In this example, we are grouping the results by the user_id column and selecting the sum of the total_amount column for each user. We use the get method to retrieve all the results, which will be returned as a collection.
Note that when using raw SQL queries, you should be careful to sanitize any user input to prevent SQL injection attacks. You can use Laravel's query builder bindings or parameterized queries to sanitize user input.
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.


 
Related posts:

Laravel if statement multiple conditions – if condition in Laravel
sql query to sum two columns values based on its record – How to SUM two fields within an SQL query?
Laravel Eloquent “WhereNotIn” Query Example
Print last query in laravel eloquent – Examples