SIGN functions

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
productbuy_pricesell_pricedifference
Product110155
Product12015-5
Product110100
Product120255

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.
productbuy_pricesell_priceDifferenceResult
Product110151Profit
Product12015-1Loss
Product110100No Prift No Loss
Product120251Profit
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
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer