Db Query groupby and sum each

i want to achieve

I am trying making the booking table suitable for report

group each by orgName and payment_gateway and sum commision column value for each date

*OrgName #1
        - LocalBook
            - Update_date 1
                    [ 
                    array_booking
                    ]
                - Count Total (LBP)
                - Total Sum(LBC) commison
            - Update_date 2
                    [ 
                    array_booking
                    ]
                - Count Total (LBP)
                - Total Sum(LBC) commison
            - Update_date 3
                .
                .

        - SelfBook
            - Update_date
                    [ 
                    array_booking
                    ]
                - Count Total (SBP)
                - Total Sum(SBC) commison

        - International Book
            - Update_date
                    [ 
                    array_booking
                    ]
                - Count Total (IBP)
                - Total Sum(IBC) commision

    *OrgName #2

bookings table

booking table as I mention I want to group by the org_id, payment_gateway and update_at.
count and sum inside date array!!

id |org_id | local_id| check_in| Checkout| commision| Pay| Payment_gateway| create_at| update_at|

my query i just try it is query what I try to make work,but not working

       $bookedStatus_id=BookStatus::where('name','Booked')->first()->id;
        $booking=Booking::with('bookable')->where('book_status_id',$bookedStatus_id)
                           ->where('pay','>',0)
                           ->selectRaw("DATE_FORMAT(Update_date, '%Y %m %e') date")
                           ->get()
                           ->groupBy(['organization_id','payment_gateway','date'])
                           ->toArray();

       // db query also try 
       $booking=DB::table('bookings')->where('book_status_id',$bookedStatus_id)
                       ->where('pay','>',0)
                        ->selectRaw("DATE_FORMAT(check_in, '%Y %m %e') date")
                       ->selectRaw("*,SUM(commison) as sum")                     
                       ->get()
                        ->groupBy(['organization_id','payment_gateway','date'])

                        ->toArray();

not working for me any help, please

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2TWKUyD
via IFTTT