How to add condtion inside DB::raw some thing like this

@Sinnbeck Not Working :(

$bookings = Booking::whereBetween('booking_date',[$date,$date_to])->whereIn('status',['collect_payment','end_visit'])->whereNotIn('user_id', $excludedUsers)->groupBy('biker_id')->select([
            'biker_id',
            DB::raw( 'COUNT(biker_id) WHERE is_subscription = 0 as "count"')
        ])->get();

gives me this error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as "count" from bookings where booking_date between ? and ? and status ...' at line 1 (SQL: select biker_id, COUNT(biker_id) WHERE is_subscription = 0 as "count" from bookings where booking_date between 2021-02-01 and 2021-02-02 and status in (collect_payment, end_visit) and user_id not in (67134, 67135, 67133, 44620, 1876, 32534, 57, 1026, 492, 103826, 23179) group by biker_id)

@JhonD Oh sorry forgot to move the where

$bookings = Booking::whereBetween('booking_date',[$date,$date_to])->whereIn('status',['collect_payment','end_visit'])->where('is_subscription', 0)->whereNotIn('user_id', $excludedUsers)->groupBy('biker_id')->select([
            'biker_id',
            DB::raw( 'COUNT(biker_id)  as "count"')
        ])->get();

@Sinnbeck thank you very much, but is there is any way to add a condition inside DB::raw not for the main query because i need some thing like this at the end

$bookings = Booking::whereBetween('booking_date',[$date,$date_to])->whereIn('status',['collect_payment','end_visit'])->whereNotIn('user_id', $excludedUsers)->groupBy('biker_id')->select([
            'biker_id',
            DB::raw( 'COUNT(biker_id) WHERE is_subscription = 0 as "count"'),
			DB::raw( 'SUM(Booking_amount) WHERE is_subscription = 0 as "normalBookingAmount"'),
			DB::raw( 'SUM(Booking_amount) WHERE is_subscription = 1 as "SubscribtionBookingAmount"'),
        ])->get();

@JhonD That would require subquery selects for each :)

https://laravel.com/docs/8.x/eloquent#subquery-selects

Or if you can use a "parent" model like Biker, you can use relationship counting/summing

https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models