Sign of the argument using Select Query in MySql

SELECT SIGN(X)

Output will -1,0,1 based on the value of X

We can get the sign of the data by using sign function. Here is an example

SELECT SIGN(-4.5); Output -1
SELECT SIGN(+4.5); Output 1
SELECT SIGN(4.5); Output 1
SELECT SIGN(0); Output 0

If we try a sting we will get 0 as output

SELECT SIGN('abc'); // Output 0

What is the output of sign(0) ?

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

More about CASE condition Query

Output is here.

product	buy_price	sell_price	Difference	Result
Product1	10	15	1	Profit
Product1	20	15	-1	Loss
Product1	10	10	0	No Prift No Loss
Product1	20	25	1	Profit

The SQL Dump of this table is here

CREATE TABLE IF NOT EXISTS `plus2_price` (
  `product` varchar(10) NOT NULL,
  `buy_price` int(3) NOT NULL,
  `sell_price` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `plus2_price`
--

INSERT INTO `plus2_price` (`product`, `buy_price`, `sell_price`) VALUES
('Product1', 10, 15),
('Product1', 20, 15),
('Product1', 10, 10),
('Product1', 20, 25);

SQL Math References ABS() to get Absolute value Standard deviation Average value Query

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

SIGN functions

CASE .. WHEN THEN with SIGN query

Subhendu Mohapatra