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();
