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;