Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Shady Hesham's avatar

how to sum with groupby and withHas in lararvel

0

i have 2 tables

invoice (id, service_inv_cat_id, etc..) payment (id, invoice_id, amount) I need to get the sum of amount in payment with groupby (service_inv_cat_id)

here is my code

$income_details_cat = Invoice::select('id', 'service_inv_cat_id')
        ->with(['service_inv_cat' => function ($q) {
            $q->select('id', 'name');}])   
        ->with(['payment' => function ($q) use ($year, $month){
            $q->select('id','invoice_id', 'amount')
            ->whereYear('paid_date', $year)
            ->whereMonth('paid_date', $month)
        ;}])
        ->whereHas('payment', function ($q) use ($year,$month) {
            return $q->where('type', 3)
            ->whereYear('paid_date', $year)
            ->whereMonth('paid_date', $month)
        ;})
        ->withSum(['payment' => function ($query) use ($year, $month){
            $query->whereYear('paid_date', $year)
            ->whereMonth('paid_date', $month);
        }], 'amount')
        ->get();

It returns

laser 80
wallet 200
wallet 100
wallet 50

but I need to get the total sum of wallet,

i have tried to add ->groupBy('service_inv_cat_id')

but it returns the following

laser 80
wallet 200

it returns the sum of payment of only one record, ex wallet has higher than 200 in total with many invoices as you can see in pic 1 but its returns only one

0 likes
0 replies

Please or to participate in this conversation.