I have column in my table where it stores prices and I need to sum those prices, the issue is I need to cut specific amount from each row before sum them.
Example
row 1, column transport = 15000 row 2, column transport = 50000 row 3, column transport = 30000 row 4, column transport = 42000 total will be = 137000
I don’t want to just sum this numbers, what I need is cut 5000 from each row value and then sum the rest.
So my total after cutting that 5000 will be 117000 and that’s the price I’m looking for.
Code
This code return
137000
$month = Order::where('user_id', $userId)->whereMonth('created_at', Carbon::now()->month)->sum('transport');
Advertisement
Answer
Instead of running 2 queries and then perform calculation you can do it in a single go as
$month = Order::where('user_id', $userId)
->whereMonth('created_at', Carbon::now()->month)
->sum(DB::raw('transport - 5000'));
Sample SQL demo just to show you the internal working of aggregate method sum()
CREATE TABLE IF NOT EXISTS `orders` (
`amount` int(11) unsigned NOT NULL
);
INSERT INTO `orders` (`amount`)
VALUES (15000),(50000),(30000),(42000);
select sum(amount),
sum(amount - 5000)
from orders
| sum(amount) | sum(amount - 5000) |
|-------------|--------------------|
| 137000 | 117000 |