Sum in laravel db query

Question

I need to get the Sum of ProductQTY groupBy ProductID while using join, I always get an error when using db::raw, attached here is my code

$pick_list_items = DB::table('pick_list_detail')
                   ->where('pick_list_detail.pick_list_id',$id)
                   ->join('sale_invoices', 'pick_list_detail.sale_invoice_id','=','sale_invoices.id')
                   ->join('sale_invoice_detail', 'sale_invoice_detail.sale_invoice_id','=','pick_list_detail.sale_invoice_id')
                   ->select(['pick_list_detail.sale_invoice_id', 'sale_invoice_detail.product_id', 'sale_invoice_detail.product_qty', 'sale_invoice_detail.uom', 'sale_invoice_detail.uom_factor'])
                   ->sum('sale_invoice_detail.product_qty')
                   ->groupBy('sale_invoice_detail.product_id')
                   ->get();

I'm using laravel 5.4

Here is the error

(2/2) QueryException SQLSTATE[42000]: Syntax error or access violation: 1055 'fdis.pick_list_detail.sale_invoice_id' isn't in GROUP BY (SQL: select pick_list_detail.sale_invoice_id, sale_invoice_detail.product_id, sale_invoice_detail.product_qty, sale_invoice_detail.uom, sale_invoice_detail.uom_factor from pick_list_detail inner join sale_invoices on pick_list_detail.sale_invoice_id = sale_invoices.id inner join sale_invoice_detail on sale_invoice_detail.sale_invoice_id = pick_list_detail.sale_invoice_id where pick_list_detail.pick_list_id = 1 group by sale_invoice_detail.product_id)

Martney Acha Martney Acha · Accepted Answer · 2018-01-29T05:56:17

    $sale_invoices = DB::table('pick_list_detail')
    ->select(DB::raw('sum(sale_invoice_detail.product_qty) as si_count, pick_list_detail.pick_list_id , sale_invoice_detail.product_id , sale_invoice_detail.uom, sale_invoice_detail.uom_factor '))
    ->where('pick_list_detail.pick_list_id',$id)
    ->join('sale_invoices', 'pick_list_detail.sale_invoice_id','=','sale_invoices.id')
    ->join('sale_invoice_detail', 'sale_invoice_detail.sale_invoice_id','=','pick_list_detail.sale_invoice_id')
    ->groupBy('pick_list_detail.pick_list_id')
    ->groupBy('sale_invoice_detail.product_id')
    ->groupBy('sale_invoice_detail.uom')
    ->groupBy('sale_invoice_detail.uom_factor')
    ->get();

Raw Query is my solution.

Sum in laravel db query

1 Answers