SUM of a column using WHERE clause in Laravel 9

Hi I really need some help with this.

am trying to use multiple Count and Sum commands in a single query. However what is happening is when i use the following:

SUM(amount AND type = "dispute") as charge

It actually counts the amount of records and does not total the amounts. What i want to do is use SUM with a where clause multiple times, so for example:

$query = DB::table('cash_table') ->where('transaction_created', '>=', Carbon::now()->startOfWeek()) ->select(DB::raw(' SUM(amount) as transaction_amount, COUNT(amount) as transaction_count, SUM(amount) WHERE type = "dispute" as charge_amount COUNT(amount) WHERE type = "dispute" as charge_count SUM(loan_amount) WHERE type = "loan" as loan_amount COUNT(loan_amount) WHERE type = "loan" as loan_count ')) ->groupBy('transaction_created') ->get();

The top two "transaction_amount & transaction count work fine as there is no where clause" The other don't.

LaryAI

3 years ago

Level 58

The issue with the provided code is that the SUM function is not being used correctly with the WHERE clause. Instead of using AND inside the SUM function, the WHERE clause should be added separately. Here's an updated version of the code:

$query = DB::table('cash_table')
    ->where('transaction_created', '>=', Carbon::now()->startOfWeek())
    ->select(DB::raw('
        SUM(amount) as transaction_amount,
        COUNT(amount) as transaction_count,
        SUM(CASE WHEN type = "dispute" THEN amount ELSE 0 END) as charge_amount,
        COUNT(CASE WHEN type = "dispute" THEN amount ELSE NULL END) as charge_count,
        SUM(CASE WHEN type = "loan" THEN loan_amount ELSE 0 END) as loan_amount,
        COUNT(CASE WHEN type = "loan" THEN loan_amount ELSE NULL END) as loan_count
    '))
    ->groupBy('transaction_created')
    ->get();

In this updated code, the SUM function is used with a CASE statement inside it to filter the rows based on the type column. The COUNT function is also updated to use CASE statement to count only the rows that match the type condition.

Please or to participate in this conversation.