MySQL remove zeros from decimal part if the number is whole-CodePudding

I have a sales table with price column as decimal(8, 2) and I'm running the query below to get the total price

select sum(case when status = 1 then price * quantity when status = 2 then price * quantity * -1 else 0 end) as total from sales;

It's working perfectly but my results are usually intergers so I end up with trailing zeros all the time. It'd be nice to just return the whole number if there is no decimal part

CodePudding user response：

like akina said in his comment ...

you can cast the sum result to UNSIGNED INTEGER

select 
CAST(sum(case when status = 1 then price * quantity when status = 2 then price * quantity * -1 else 0 end) AS UNSIGNED)
 as total from sales;