MySQL SIGN() function

SIGN(X) returns -1, 0, or 1 depending on whether X is negative, zero, or positive.

SELECT SIGN(X);

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

Current MySQL can coerce a nonnumeric string to zero while issuing a warning. The following legacy behavior is shown so you can recognize it, but application queries should use numeric data explicitly.

SELECT SIGN('abc'); -- Result may be 0 with a conversion warning; avoid this pattern

Profit or loss using a price difference

SELECT product, buy_price, sell_price,
       sell_price - buy_price AS difference
FROM plus2_price;

CASE with SIGN()

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 Profit No Loss'
         WHEN -1 THEN 'Loss'
       END AS Result
FROM plus2_price;

See CASE expressions for more conditional-query examples.

Sample table

CREATE TABLE IF NOT EXISTS plus2_price (
  product VARCHAR(10) NOT NULL,
  buy_price INT NOT NULL,
  sell_price INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

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: