SQL Server SIGN() Function

Summary: in this tutorial, you will learn how to use the SQL Server SIGN() function to obtain the sign of a number.

SQL Server SIGN function syntax

In SQL Server, the SIGN() function allows you to retrieve the sign of a number, which can be either negative, positive, or zero.

Here’s the syntax of the SIGN() function:

SIGN (numeric_expression)Code language: SQL (Structured Query Language) (sql)

In this syntax, the numeric_expression is a number or a numeric expression that you want to obtain the sign.

The SIGN() function returns a 1 if the value is positive, -1 if the value is negative, or 0 if the value is zero.

The function will return NULL if the numeric_expression is NULL.

SQL Server SIGN() function examples

Let’s take some examples of using the SIGN() function.

1) Basic SIGN() function example

The following example uses the SIGN() function to retrieve the sign of the numbers -10, 0, and 10:

SELECT
  SIGN (-10) s1,
  SIGN (0)   s2,
  SIGN (10)  s3;Code language: SQL (Structured Query Language) (sql)

Output:

s1 | s2 | s3
---+----+----
-1 | 0  | 1Code language: SQL (Structured Query Language) (sql)

2) Using the SIGN() function with table data

We’ll use the sales.orders and sales.order_items tables from the sample database:

The following query uses the SIGN() function to create sales trends based on the signs of the sales variances between a year and the previous year:

WITH
  sales_summary(year, amount) AS (
    SELECT
      YEAR (order_date) year,
      SUM(quantity * list_price * (1 - discount)) amount
    FROM
      sales.orders o
      INNER JOIN sales.order_items i ON i.order_id = o.order_id
    GROUP BY
      YEAR (order_date)
  ),
  sales_comparison(year, amount, py_sales) AS (
    SELECT
      year,
      amount,
      LAG(amount) OVER ( ORDER BY year) AS py_sales
    FROM
      sales_summary
  )
SELECT
  year,
  amount,
  py_sales,
  CASE
    WHEN py_sales IS NULL THEN 'N/A'
    WHEN sign (amount - py_sales) = 1 THEN 'Up'
    WHEN sign (amount - py_sales) = -1 THEN 'Down'
    ELSE 'Unchanged'
  END AS sales_variances
FROM
  sales_comparison;Code language: SQL (Structured Query Language) (sql)

Output:

year | amount       | py_sales     | sales_variances
-----+--------------+--------------+----------------
2016 | 2427378.5276 | None         | N/A
2017 | 3447208.2425 | 2427378.5276 | Up
2018 | 1814529.7875 | 3447208.2425 | DownCode language: SQL (Structured Query Language) (sql)

How it works.

First, create a common table expression (CTE) called sales_summary that returns the total sales by year:

SELECT
  YEAR (order_date) year,
  SUM(quantity * list_price * (1 - discount)) amount
FROM
  sales.orders o
  INNER JOIN sales.order_items i ON i.order_id = o.order_id
GROUP BY
  YEAR (order_date)Code language: SQL (Structured Query Language) (sql)

Second, create another CTE called sales_comparison that includes the previous sales (py_sales) using the LAG() window function:

SELECT
  YEAR,
  amount,
  LAG (amount) OVER ( ORDER BY year) AS py_sales
FROM
  sales_summaryCode language: SQL (Structured Query Language) (sql)

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:

SELECT
  year,
  amount,
  py_sales,
  CASE
    WHEN py_sales IS NULL THEN 'N/A'
    WHEN sign (amount - py_sales) = 1 THEN 'Up'
    WHEN sign (amount - py_sales) = -1 THEN 'Down'
    ELSE 'Unchanged'
  END AS sales_variances
FROM
  sales_comparison;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the SIGN() function to obtain the sign of a number.