Get sum value on where group

Viewed 280

I need total_user male and female for my return data, it's possible using WHERE on GET sum?

I'm trying for this code but not work

$male = $get_matket_penetration->where('gender', 'MALE')->sum('totalUser');
$female = $get_matket_penetration->where('gender', 'FEMALE')->sum('totalUser');
$male_persentation = round($male /($male+$female) * 100, 2);
$female_persentation = round($female /($male+$female) * 100, 2);

This is my full code

$get_matket_penetration = MarketOverviewMarketPenetrationMap::dashboardtypeid(session('user_dashboard_type'))->provinceid($user_province_id)->dates($year_month)
        ->select('province',DB::raw('SUM(totalUser) as totalUser'))
        ->groupBy('province')
        ->groupBy('gender')
        ->get();

        $ecommerce_penetration_map = [];
        foreach($get_matket_penetration->toArray() as $item):
            // Get total User Per Province
            $total_per_province = TselPenetration::cityid(session('user_city_id'))->dates($year_month)
                ->select(DB::raw('SUM(totalUser) as totalUser'))
                ->where('province',$item['province'])
                ->first();
            // Set Penetration user per app per province
            $penetration = ($item['totalUser']/$total_per_province->totalUser) * 100;
            $male = $get_matket_penetration->where('gender', 'MALE')->sum('totalUser');
            $female = $get_matket_penetration->where('gender', 'FEMALE')->sum('totalUser');
            $male_persentation = round($male /($male+$female) * 100, 2);
            $female_persentation = round($female /($male+$female) * 100, 2);
            $ecommerce_penetration_map[$item['province']] = [
                'province' => $item['province'],
                'total_user' => $item['totalUser'],
                'male'=> $male_persentation ,
                'female'=> $female_persentation ,
                'total_user_province'=>$total_per_province->totalUser,
                'penetration'=>round($penetration, 2)
            ];
        endforeach;

   dd($ecommerce_penetration_map );
1 Answers

in your $get_matket_penetration you have selected 2 columns. 1-province 2-totalUser so you don't have a gender for where('gender', 'MALE').

Related