How to get sum and separate the total per Id in laravel

This is my query in my controller

  public function show($id)
    {
        $Days = DB::table('payroll_monthly_attn_project')->where('payroll_monthly_id',$id)->where('assign','PAINTING')
        ->select('payroll_monthly_attn_project.wrk_id','payroll_monthly_attn_project.project_code','payroll_monthly_attn_project.assign','payroll_monthly_attn_project.days');

        $Monthly_details = DB::table('payroll_monthly_payable_details')->where('payroll_monthly_payable_details.payroll_monthly_id', $id)
                           ->select('payroll_monthly_payable_details.daily_rate','payroll_monthly_payable_details.late','payroll_monthly_payable_details.ut','payroll_monthly_payable_details.absent','days.*')
                           ->leftjoinSub($Days,'days',function($join){
                            $join->on('payroll_monthly_payable_details.wrk_id','=','days.wrk_id');    

                           })
                        ->get();
   
        return $Monthly_details;
    }

Result of my query… I want to get total of separate project_code

0: {daily_rate: 8.63, late: 0, ut: 0, absent: 0, wrk_id: 3, project_code: "CMUN", assign: "PAINTING",…}
absent: 0
assign: "PAINTING"
daily_rate: 8.63
days: 13
late: 0
project_code: "CMUN"
ut: 0
wrk_id: 3
1: {daily_rate: 575.08, late: 0, ut: 0, absent: 0, wrk_id: 2, project_code: "N2", assign: "PAINTING",…}
absent: 0
assign: "PAINTING"
daily_rate: 575.08
days: 7
late: 0
project_code: "N2"
ut: 0
wrk_id: 2
2: {daily_rate: 57.51, late: 0, ut: 0, absent: 0, wrk_id: 4, project_code: "N2", assign: "PAINTING",…}
absent: 0
assign: "PAINTING"
daily_rate: 57.51
days: 13
late: 0
project_code: "N2"
ut: 0
wrk_id: 4

Also I tried this but it only get the sum of all

  public function show($id)
    {
 
        $Days = DB::table('payroll_monthly_attn_project')->where('payroll_monthly_id',$id)->where('assign','PAINTING')
        ->select('payroll_monthly_attn_project.wrk_id','payroll_monthly_attn_project.project_code','payroll_monthly_attn_project.assign','payroll_monthly_attn_project.days');

        $Monthly_details = DB::table('payroll_monthly_payable_details')->where('payroll_monthly_payable_details.payroll_monthly_id', $id)
                           ->select('payroll_monthly_payable_details.daily_rate','payroll_monthly_payable_details.late','payroll_monthly_payable_details.ut','payroll_monthly_payable_details.absent','days.*')
                           ->leftjoinSub($Days,'days',function($join){
                            $join->on('payroll_monthly_payable_details.wrk_id','=','days.wrk_id');    

                           })
                        ->get();

        $fetch = [];
        $grandTotal = 0;
        foreach($Monthly_details as $key){

            $grandTotal += ($key->daily_rate * $key->days) - $key->absent - $key->late - $key->ut;

        }
        $fetch['grandTotal'] = $grandTotal;

        return $fetch;
    }

Result of my query..

{grandTotal: 4885.38}
grandTotal: 4885.38

Result I want to achieve..

"CMUN"{project_code:"CMUN"
total:112.19},
"N2"{project_code:"N2"
total:4773.19}

Answer

You don’t need to change the query. Just change the foreach loop code.

foreach($Monthly_details as $key){
    if(empty($fetch[$key->project_code])){
        $fetch[$key->project_code]['total'] = 0;
    }
    $fetch[$key->project_code]['total'] += ($key->daily_rate * $key->days) - $key->absent - $key->late - $key->ut;
}
return $fetch;

// The Section which calls the function show().

$results = work($id);
foreach($results as $projectCode => $total){
   echo $projectCode." total cost is: ".$total;
}

Advertisement

Answer