how to group, count and sum data with laravel eloquent?-CodePudding

I need to obtain the total number of packages sold in a certain date range and the total amount of money they generated.

I am trying with this code that I have made

$compras = DB::table('transacciones')
    ->select('transacciones.id','packages.name as nombre_paquete',DB::raw('count(packages.name) as quantity sold'),
    DB::raw('sum(packages.price) as total'))
    ->join('packages','transacciones.package_id','=','packages.id')
    ->where('packages.region_id',$region_id)
    ->whereBetween('transacciones.created_at',[$request->fecha_inicio,$request->fecha_fin])
    ->groupBy('packages.name')
    ->get();

but the result shows me an error in the query

error

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation:
 1064 You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near &#039;sold, sum(packages.price) 
as total from `transacciones` inner join `packages` on&#039; 
at line 1 (SQL: select `transacciones`.`id`, `packages`.`name` as `nombre_paquete`, 
count(packages.name) as quantity sold, sum(packages.price) as 
total from `transacciones` 
inner join `packages` on `transacciones`.`package_id` = `packages`.`id` 
where `packages`.`region_id` = 1 and `transacciones`.`created_at` between 
2022-01-01 and 2022-08-31 group by `transacciones`.`id`, `packages`.`name`) 
in file C:\wamp\www\general-api\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 712

CodePudding user response：

You have made a syntax mistake. Use this. I hope works

$compras = DB::table('transacciones') ->select(
'transacciones.id',
'packages.name as nombre_paquete',
DB::raw('count(packages.name) as quantitysold'),
DB::raw('sum(packages.price) as total')
)
->join('packages','transacciones.package_id','=','packages.id') ->where('packages.region_id',$region_id) ->whereBetween('transacciones.created_at',[$request->fecha_inicio,$request->fecha_fin]) ->groupBy('packages.name') ->get();