Laravel cut amount from rows before sum

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');

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 |