How to Perform Calculations Using SELECT Statement in T-SQL

Multiplication, division and modulo are evaluated before addition and subtraction. Operators of equal precedence are evaluated left to right. Parentheses override both:

SELECT 2 + 3 * 4    AS no_parentheses,     -- 14
       (2 + 3) * 4  AS with_parentheses;   -- 20

Integer division

When both operands are integer types, / returns an integer and discards the fraction. It truncates toward zero. It does not round.

SELECT 7 / 2      AS a,   -- 3
       -7 / 2     AS b,   -- -3
       1 / 2      AS c,   -- 0
       7 / 2.0    AS d,   -- 3.500000
       7 * 1.0 / 2 AS e;  -- 3.5, displayed with trailing zeros

To get a fractional result, make at least one operand a decimal before the division happens. With columns, use CAST:

SELECT CAST(Delivered AS decimal(10,2)) / Ordered AS fill_rate
FROM dbo.Shipments;

Order of evaluation matters when you calculate percentages with integers:

SELECT 45 / 60 * 100   AS wrong,   -- 0, because 45 / 60 is 0
       100 * 45 / 60   AS better,  -- 75
       100.0 * 45 / 60 AS best;    -- 75, as a decimal with trailing zeros

How SQL Server picks the result type

When the two operands have different types, the one with lower precedence is converted to the other. The order that matters for arithmetic is, from low to high: tinyint, smallint, int, bigint, money, decimal, real, float. So int + decimal is decimal, and decimal + float is float.

A literal with a decimal point, such as 2.0, is a decimal. A literal without one, such as 2, is an int. Character strings rank below numbers, which leads to this:

SELECT '5' + 5;          -- 10: the string is converted to int
SELECT 'Total: ' + 5;    -- error

Msg 245: Conversion failed when converting the varchar value 'Total: ' to data type int.

+ is both addition and string concatenation, and with one number involved it always means addition. To build a string, use CONCAT('Total: ', 5), which converts its arguments for you.

For decimal results, SQL Server works out precision and scale from the operands. Multiplication adds the scales, which is why 13.7858 * 3.58 has six decimal places. Division produces at least six. You rarely want the raw result, so cast the final answer to the scale you need.

Calculations on columns

CREATE TABLE dbo.OrderLines (
    OrderLineId int           NOT NULL PRIMARY KEY,
    Product     varchar(50)   NOT NULL,
    Quantity    int           NOT NULL,
    UnitPrice   decimal(10,2) NOT NULL,
    DiscountPct decimal(5,2)  NULL
);

INSERT INTO dbo.OrderLines VALUES
(1, 'Keyboard',  3,  19.99, NULL),
(2, 'Monitor',   2, 149.50, 10.00),
(3, 'Cable',    10,   4.25,  0.00),
(4, 'Dock',      1,  89.00, 15.00);

SELECT Product,
       Quantity * UnitPrice AS LineTotal,
       CAST(Quantity * UnitPrice * (1 - DiscountPct / 100) AS decimal(10,2)) AS NetTotal
FROM dbo.OrderLines;

Product   LineTotal  NetTotal
--------  ---------  --------
Keyboard  59.97      NULL
Monitor   299.00     269.10
Cable     42.50      42.50
Dock      89.00      75.65

The expression is evaluated once per row. Aggregates work over expressions too: SELECT SUM(Quantity * UnitPrice) FROM dbo.OrderLines; returns 490.47.

NULL makes the whole result NULL

The Keyboard row has no discount recorded, and its NetTotal is NULL. Any arithmetic involving NULL returns NULL, because the value is unknown. Supply a default where one makes sense:

SELECT Product,
       CAST(Quantity * UnitPrice * (1 - ISNULL(DiscountPct, 0) / 100) AS decimal(10,2)) AS NetTotal
FROM dbo.OrderLines;

The Keyboard row now returns 59.97. COALESCE(DiscountPct, 0) is the standard SQL equivalent. Only substitute a default when "missing" really means zero. Aggregates such as SUM and AVG skip NULLs, so AVG(DiscountPct) averages three rows here, not four.

Division by zero

SELECT 10 / 0;

Msg 8134: Divide by zero error encountered.

One row with a zero divisor fails the whole statement. The usual fix is NULLIF, which turns the zero into NULL so the result is NULL instead of an error:

SELECT Product,
       UnitPrice / NULLIF(Quantity, 0) AS PricePerUnit
FROM dbo.OrderLines;

Wrap it in ISNULL(..., 0) if you want zero back. Avoid SET ARITHABORT OFF and SET ANSI_WARNINGS OFF as a workaround: they hide the error for everything in the session and break features such as indexed views.

Overflow

Integer arithmetic stays in the integer type, even when the answer does not fit:

SELECT 2147483647 + 1;

Msg 8115: Arithmetic overflow error converting expression to data type int.

Convert one operand to a larger type first: SELECT CAST(2147483647 AS bigint) + 1; returns 2147483648. The same applies to SUM over an int column with a large total (cast the column inside the SUM), and to COUNT, which has a COUNT_BIG counterpart.

Rounding

ROUND rounds the value but keeps the data type, so the trailing digits remain. CAST to a smaller scale rounds and shortens:

SELECT ROUND(123.456, 2)                 AS a,   -- 123.460
       CAST(123.456 AS decimal(10,2))    AS b,   -- 123.46
       ROUND(123.456, 2, 1)              AS c,   -- 123.450 (third argument truncates)
       ROUND(123.456, -1)                AS d,   -- 120.000
       CEILING(123.456)                  AS e,   -- 124
       FLOOR(123.456)                    AS f;   -- 123

decimal values round half away from zero: ROUND(2.5, 0) is 3.0 and ROUND(-2.5, 0) is -3.0.

Round once, at the end. Rounding each row and then summing gives a different total from summing and then rounding.

Use decimal for money, not float

float and real are binary approximations. They cannot represent most decimal fractions exactly:

SELECT CASE WHEN CAST(0.1 AS float) + CAST(0.2 AS float) = CAST(0.3 AS float)
            THEN 'equal' ELSE 'not equal' END;   -- not equal

Use decimal (also spelled numeric) for prices, quantities and anything you compare for equality or have to reconcile. Keep float for scientific measurements. The money type is exact but has only four decimal places, and intermediate results of money division are truncated to four places as well, so many teams prefer decimal for calculations.

Reusing a calculated value

A column alias cannot be referenced elsewhere in the same SELECT list or in WHERE:

SELECT Quantity * UnitPrice AS LineTotal,
       LineTotal * 0.2      AS Tax        -- Invalid column name 'LineTotal'
FROM dbo.OrderLines;

Define the value once with CROSS APPLY, then use it freely:

SELECT ol.Product, c.LineTotal,
       CAST(c.LineTotal * 0.2 AS decimal(10,2)) AS Tax
FROM dbo.OrderLines AS ol
CROSS APPLY (SELECT ol.Quantity * ol.UnitPrice AS LineTotal) AS c
WHERE c.LineTotal > 50;

A CTE or derived table works as well. If the same expression is needed in many queries, add it to the table as a computed column:

ALTER TABLE dbo.OrderLines ADD LineTotal AS (Quantity * UnitPrice);

Performance

Arithmetic in the SELECT list costs almost nothing. Arithmetic on a column in the WHERE clause is different, because it stops SQL Server from seeking on an index on that column:

WHERE UnitPrice * 1.2 > 100    -- scans every row
WHERE UnitPrice > 100 / 1.2    -- can use an index on UnitPrice

Move the calculation to the other side of the comparison. If you must filter on a computed expression, create a computed column for it and index that.

Other math functions

ABS, POWER, SQRT, SIGN, LOG, EXP and the trigonometric functions are built in. Note that POWER returns the type of its first argument, so POWER(2, 0.5) returns 1. Write POWER(2.0, 0.5) or use SQRT.

Further reading

• Arithmetic operators (Transact-SQL)
• Data type precedence (Transact-SQL)
• Precision, scale, and length (Transact-SQL)
• Mathematical functions (Transact-SQL)

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting