DB raw query issue - cannot get count

I tried this:

->select(
DB::raw("COUNT(CASE status WHEN 'active' THEN 1 ELSE NULL END) AS active_games)"),
DB::raw("COUNT(CASE status WHEN 'pending' THEN 1 ELSE NULL END) AS pending_games)")
)

But it fails. I also tried this:

 ->select(
DB::raw("SUM(CASE status WHEN 'active' THEN 1 ELSE NULL END) AS active_games)"),
DB::raw("SUM(CASE status WHEN 'pending' THEN 1 ELSE NULL END) AS pending_games)")
)

But it fails

It does not give much information to work with; how does this fail? Also, I specifically used SUM because I want to SUM the 1s and 0s in the CASE statements.

Sorry:

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 '), COUNT(CASE status WHEN 'pending' THEN 1 ELSE NULL END) AS pending_games)

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 '), SUM(CASE status WHEN 'pending' THEN 1 ELSE NULL END) AS pending_games)

Why did you add an extra closing parenthesis? There is one opening paren; and two closing parens in your code:

SUM(CASE status WHEN 'pending' THEN 1 ELSE NULL END) AS pending_games)

The original query I provided should work, don't change it.

Very well spotted, thank you so much!

For how long have you been coding Tykus? You are so good! You find so many issues for so many people in all threads......Are you a Senior full-stack? Hat off.

I tried this already:

->withCount([
    'sales as completed_sales_count' => function ($query) {
        $query->where('type', 'completed');
    },
    'sales as pending_sales_count' => function ($query) {
        $query->where('type', 'pending');
    }
])

But it is telling me that it is not in use Illuminate\Support\Facades\DB;

This looks like it works only for Eloquent and not DB query

Just use eloquent.

$users = User::withCount([
    'sales as completed_sales_count' => function ($query) {
        $query->where('type', 'completed');
    },
    'sales as pending_sales_count' => function ($query) {
        $query->where('type', 'pending');
    }
])->select('users.*')
    ->leftjoin('games', 'users.id', '=', 'games.user_id')
    ->orderBy($search, $order)
    ->paginate(100);

I cannot, unfortunately. The query I am working on is massive and has been built long ago with DB query. To redo it all....ouch. The query deals with multiple search bars and about 8 table relations.

I like when people don't provide a full query and they want to help. Bye.

I simply removed all the relations but the query is the same.....in the past I pasted long queries and got shouted at by other users.

Thanks!