Question 80239 in Laravel Question posted in
The official Laravel Documentation can be found here.

SUM of a column using WHERE clause in Laravel 9

I 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.

Tags:

2

Answers


  1. 0

    Try below:

    $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(if(type = "dispute", amount, 0)) as charge_amount
            COUNT(type = "dispute") as charge_count
            SUM(if(type = "loan", amount, 0)) as loan_amount
            COUNT(type = "loan")as loan_count
            '))
    ->groupBy('transaction_created')
    ->get();
    Login or Signup to reply.
  2. 0

    Try below!

    $query = DB::table('cash_table')
            ->where('transaction_created', '>=', Carbon::now()->startOfWeek())->select(
                DB::raw('SUM(CASE WHEN type = "dispute" THEN amount ELSE 0 END) as charge'),
                DB::raw('SUM(CASE WHEN type = "refund" THEN amount ELSE 0 END) as refund'),
                DB::raw('COUNT(CASE WHEN status = "approved" THEN 1 ELSE NULL END) as transaction_count'),
                DB::raw('SUM(amount) as transaction_amount')
            )
            ->groupBy('transaction_created')
            ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search