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,
->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
$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.