I want to use count and sum together on DB Query not sure how to go about it. I've tried several combinations but keep getting an error. I know I can just use a raw query but would like to learn how to use it correctly
Working:
DB::select('SELECT count(*) AS order_count, sum(total_including_vat) AS orders_total FROM orders WHERE user_id =' .$userProfile->id); Not Working
DB::table('orders')->where('user_id', '=', $userProfile->id)->count()->Sum(); count same as aggregates returns single value so
// you try to call method sum on number and it fails DB::table('orders')->where('user_id', $userProfile->id)->count()->sum(); you can make two requests to get sum and count but its not a good idea, or get data in collection and let it do the math
// not a good idea //$count = DB::table('orders')->where('user_id', $userProfile->id)->count(); //$sum = DB::table('orders')->where('user_id', $userProfile->id)->sum('total_including_vat'); //collection way $orders = DB::table('orders') ->where('user_id',$userPorfile->id) ->get(['id', 'total_including_vat']); $result = [ 'order_count' => $orders->count(), 'orders_total' => $orders->sum('total_including_vat') ]; or the same result as for your working example with mix raw expressions
$result = DB::table('orders') ->where('user_id', $userProfile->id) ->selectRaw('count(1) as order_count, sum(total_including_vat) as orders_total') ->first(); Use DB::raw
DB::raw('count(process.id) AS count_processes') Query:
$data = User::select('users.id', 'users.name', 'users.email', 'users.phone', 'users.created_at', DB::raw('count(process.id) AS count_processes')) ->leftJoin('process', 'users.id' , '=', 'process.client_id') ->where('users.type', $request->query('type')) ->groupBy('users.id') ->orderBy('users.created_at', 'DESC')->skip($offset*$limit)->take($limit)->get(); 