Let us use our Product Price table where we stored buying price and selling price of each product. We will find out what is the difference between the Price and it is profit or loss.
First let us know the difference in price.
SELECT product, buy_price, sell_price, sell_price - buy_price AS difference FROM plus2_price
product
buy_price
sell_price
difference
Product1
10
15
5
Product1
20
15
-5
Product1
10
10
0
Product1
20
25
5
CASE .. WHEN THEN with SIGN query
SELECT product, buy_price, sell_price,SIGN( sell_price - buy_price ) AS Difference, CASE SIGN( sell_price - buy_price ) WHEN 1 THEN 'Profit' WHEN 0 THEN 'No Prift No Loss' WHEN -1 THEN 'Loss'
END AS Result FROM plus2_price