php - Larave conditional aggregation to get multiple sum of amount

Question

Hope all is well.

Anyone can tell me how can I get to run this SQL query in a the right way in Laravel?

Controller:

     $data=DB::raw("SELECT name as 'name' FROM invoices WHERE country='$country';
            SELECT SUM(amount) as 'income' FROM invoices WHERE (country='$country' AND type='income');
            SELECT SUM(amount) as 'outcome' FROM invoices WHERE (country='$country' AND type='outcome')")
            ->groupBy('name')
            ->get();

        return view('accounting.accounts')
        ->with('accounts',$data);

I expect to use it in my view as follows:

@foreach($accounts as $account)
       <tr>
        <th>{{$account->name}}</th>
        <td>{{$account->income}}</td>
        <td>{{$account->outcome}}</td>
       </tr>
@endforeach
    

I'm new to Laravel, I would appreciate your help. Thank you in advance.

question from:https://stackoverflow.com/questions/65891309/larave-conditional-aggregation-to-get-multiple-sum-of-amount

Answer 1

I believe you want an agrregated query with conditional sum of amount for each user within a specific country. In pure SQL it can done using CASE statements as

select  name,
        sum(case when type='income' then amount else 0 end) as income,
        sum(case when type='outcome' then amount else 0 end) as outcome
from invoices
where country = :country
group by name
order by name

In query builder it can be tranformed as

$accounts= DB::table("invoices")
            ->where("country", $country)
            ->select([ "name",
                        DB::raw("sum(case when type='income' then amount else 0 end) as income"),
                        DB::raw("sum(case when type='outcome' then amount else 0 end) as outcome")
                    ])
            ->groupBy("name")
            ->orderBy("name")
            ->get();