Laravel use sum with a few conditions-CodePudding

I have a Sale model with price, quantity and status attributes

I want to return the total income from the sales table (the query below returns the result I want)

SELECT SUM(case when status = 1 then price * quantity when status = 2 then price * quantity * -1 else 0 end) as total
FROM sales;

I tried using select(DB::raw()) but it didn't return a number since I couldn't use the sum() function

CodePudding user response：

Suppose you have a Sale model, try something like :

$result = Sale::select(DB::raw('SUM(case when status = 1 then price * quantity when status = 2 then price * quantity * -1 else 0 end) as total'))
    ->get();