Select All from table and group by specific column

can u post the table structure?

@extjac Structure already added

Someone should kindly help me out with this

Table Structure

id, room_id, user_id, transaction_id, reference, price, discount_price, referral_income, referral, status

try removing bookings. from ->groupBy('bookings.referral')-

@Snapey I've tried it it still the same error

Do you have a $with statement in your model?

No I don't have it. This is my model

<?php

namespace App;
use App\User;
use App\Hostel;
use Mail;
use App\Mail\NewBooking;
use App\Mail\BookingSucessful;
use App\Mail\BookingFailed;
use App\Mail\Invoice;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Booking extends Model {
    
    use SoftDeletes;

    protected $fillable =['user_id', 'hostel_id', 'transaction_id', 'user_type', 'inspection_date', 'move_in_date', 'cleaning_services', 'information', 'status', 'room', 'price', 'discount_price','referral_income','referral', 'channel', 'reference' ];

    protected $dates = ['deleted_at'];

    public function user() {
        return $this->belongsTo(User::class);
    }

    public function getOwnerPhone($id) {
        $user = User::select('phone')->whereId($id)->first();
        return $user->phone;
    }

    public function getOwnerEmail($id) {
        $user = User::select('email')->whereId($id)->first($id);
        return $user->email;
    }

    public function getBookerPhone($id) {
        $user = User::select('phone')->whereId($id)->first();
        return $user->phone;
    }

    public function getBookerEmail($id) {
        $user = User::select('email')->whereId($id)->first($id);
        return $user->email;
    }

    public function getBookedHostel($id) {
        return $hostel = Hostel::find($id);
    }
    
    public function booker() {
        return  $this->belongsTo(User::class);
    }

    
    public function hostel() {
        return $this->belongsTo(Hostel::class);
    }


    public function referalIncome($refId) {
        return $refIncome= Booking::where('referral', $refId)->sum('referral_income');

    }

    public function hostelDetails($id) {
        return Hostel::find($id);
    }

    public function finalizeBooking($data){
        
    }
    
    public function successfulBookingMail($data) {
        return Mail::to($data['user_email'])->send(new BookingSucessful($data));
    }

    public function successfulBookingToOwner($data) {
        return Mail::to($data['user_email'])->send(new NewBooking($data));
    }
    

    public function failedBookingMail($data) {      
        return Mail::to($data['user_email'])->send(new BookingFailed($data));
    }


    public function successfulInvoiceMail($data) {
        return Mail::to($data['user_email'])->send(new Invoice($data));
    }

    
}

If there's a way I can optimize this model I'll be glad to.

try this and show me the error.

$referrals = Booking::groupBy('referral')->get();

Try editing your config/database.php file:

In connections > mysql, set 'strict' => false and see if that helps.

@samalapsy I know it is a 11 month old post. Just checking, did you manage to find the solution for this?

@bmm You have to use a subquery JOIN.

Take a look at this SO question: https://stackoverflow.com/q/50859112/4848587

What's your query?

i know it is late but try $referrals = Booking::whereNotNull('referral')->orderBy('referral_income', 'DESC')->get()->groupBy('referral');