193 votes
1 answers
php - Larave conditional aggregation to get multiple sum of amount
Get the solution ↓↓↓
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.
Undefined asked
336
votes
Answer
Solution:
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 usingCASE 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();
Share solution ↓
Additional Information:
Date the issue was resolved:
Link To Answer People are also looking for solutions of the problem: warning: a non-numeric value encountered in
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.