{"id":3733,"date":"2024-04-04T15:32:29","date_gmt":"2024-04-04T08:32:29","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3733"},"modified":"2024-04-04T15:32:42","modified_gmt":"2024-04-04T08:32:42","slug":"sql-server-sign","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-math-functions\/sql-server-sign\/","title":{"rendered":"SQL Server SIGN() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server SIGN()<\/code> function to obtain the sign of a number.<\/p>\n\n\n\n

SQL Server SIGN function syntax #<\/a><\/h2>\n\n\n\n

In SQL Server, the SIGN()<\/code> function allows you to retrieve the sign of a number, which can be either negative, positive, or zero.<\/p>\n\n\n\n

Here’s the syntax of the SIGN()<\/code> function:<\/p>\n\n\n

SIGN (numeric_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the numeric_expression<\/code> is a number or a numeric expression that you want to obtain the sign.<\/p>\n\n\n\n
The SIGN()<\/code> function returns a 1 if the value is positive, -1 if the value is negative, or 0 if the value is zero.<\/p>\n\n\n\n
The function will return NULL<\/code> if the numeric_expression<\/code> is NULL<\/code>.<\/p>\n\n\n\n
SQL Server SIGN() function examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the SIGN()<\/code> function.<\/p>\n\n\n\n
1) Basic SIGN() function example #<\/a><\/h3>\n\n\n\n
The following example uses the SIGN()<\/code> function to retrieve the sign of the numbers -10, 0, and 10: <\/p>\n\n\n
SELECT<\/span>\n  SIGN<\/span> (-10<\/span>) s1,\n  SIGN<\/span> (0<\/span>)   s2,\n  SIGN<\/span> (10<\/span>)  s3;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
s1 | s2 | s3\n---+----+----<\/span>\n-1 | 0  | 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using the SIGN() function with table data #<\/a><\/h3>\n\n\n\n
We’ll use the sales.orders<\/code> and sales.order_items<\/code> tables from the sample database:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following query uses the SIGN()<\/code> function to create sales trends based on the signs of the sales variances between a year and the previous year:<\/p>\n\n\n
WITH<\/span>\n  sales_summary(year<\/span>, amount) AS<\/span> (\n    SELECT<\/span>\n      YEAR<\/span> (order_date) year<\/span>,\n      SUM<\/span>(quantity * list_price * (1<\/span> - discount)) amount\n    FROM<\/span>\n      sales.orders o\n      INNER<\/span> JOIN<\/span> sales.order_items i ON<\/span> i.order_id = o.order_id\n    GROUP<\/span> BY<\/span>\n      YEAR<\/span> (order_date)\n  ),\n  sales_comparison(year<\/span>, amount, py_sales) AS<\/span> (\n    SELECT<\/span>\n      year<\/span>,\n      amount,\n      LAG(amount) OVER<\/span> ( ORDER<\/span> BY<\/span> year<\/span>) AS<\/span> py_sales\n    FROM<\/span>\n      sales_summary\n  )\nSELECT<\/span>\n  year<\/span>,\n  amount,\n  py_sales,\n  CASE<\/span>\n    WHEN<\/span> py_sales IS<\/span> NULL<\/span> THEN<\/span> 'N\/A'<\/span>\n    WHEN<\/span> sign<\/span> (amount - py_sales) = 1<\/span> THEN<\/span> 'Up'<\/span>\n    WHEN<\/span> sign<\/span> (amount - py_sales) = -1<\/span> THEN<\/span> 'Down'<\/span>\n    ELSE<\/span> 'Unchanged'<\/span>\n  END<\/span> AS<\/span> sales_variances\nFROM<\/span>\n  sales_comparison;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
year | amount       | py_sales     | sales_variances\n-----+--------------+--------------+----------------<\/span>\n2016 | 2427378.5276 | None         | N\/A\n2017 | 3447208.2425 | 2427378.5276 | Up\n2018 | 1814529.7875 | 3447208.2425 | Down<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
SELECT<\/span>\n  YEAR<\/span> (order_date) year<\/span>,\n  SUM<\/span>(quantity * list_price * (1<\/span> - discount)) amount\nFROM<\/span>\n  sales.orders o\n  INNER<\/span> JOIN<\/span> sales.order_items i ON<\/span> i.order_id = o.order_id\nGROUP<\/span> BY<\/span>\n  YEAR<\/span> (order_date)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  YEAR<\/span>,\n  amount,\n  LAG (amount) OVER<\/span> ( ORDER<\/span> BY<\/span> year<\/span>) AS<\/span> py_sales\nFROM<\/span>\n  sales_summary<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, categorize the sales variances as “Up” if the current year’s sales are higher, “Down” if it is lower, and “Unchanged” if it is the same as the previous year:<\/p>\n\n\n
SELECT<\/span>\n  year<\/span>,\n  amount,\n  py_sales,\n  CASE<\/span>\n    WHEN<\/span> py_sales IS<\/span> NULL<\/span> THEN<\/span> 'N\/A'<\/span>\n    WHEN<\/span> sign<\/span> (amount - py_sales) = 1<\/span> THEN<\/span> 'Up'<\/span>\n    WHEN<\/span> sign<\/span> (amount - py_sales) = -1<\/span> THEN<\/span> 'Down'<\/span>\n    ELSE<\/span> 'Unchanged'<\/span>\n  END<\/span> AS<\/span> sales_variances\nFROM<\/span>\n  sales_comparison;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SIGN()<\/code> function to obtain the sign of a number.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, create another CTE called sales_comparison<\/code> that includes the previous sales (py_sales<\/code>) using the LAG()<\/a><\/code> window function:<\/p>\n\n\n
First, create a common table expression<\/a> (CTE) called sales_summary<\/code> that returns the total sales by year:<\/p>\n\n\n