Calculations in Columns

Work out new values as you query, with arithmetic on a row's columns and joined text, and give each calculated column a name.

Apprentice · T-SQL · SQL · Updated

After this lesson you can

  • Calculate a new column from the values in each row

Key takeaways

  • A calculated column works out a value from the columns in the same row, such as UnitPrice * UnitsInStock.
  • Always give a calculation an alias with AS, or it comes back with no column name.
  • As in C#, dividing one whole number by another drops the remainder: 7 / 2 is 3, and 7 / 2.0 keeps the decimals.
  • + joins text, and text goes in single quotes, as in FirstName + ' ' + LastName.
  • CONCAT joins text too, and also takes numbers, such as UnitsInStock, and empty values without errors.

A query can return more than the stored columns: it can calculate new values as it goes. Each calculation uses the values in the same row.

Arithmetic

How much stock of each product does the company hold, in dollars? Multiply the price by the units in stock:

SELECT ProductName
    , UnitPrice
    , UnitsInStock
    , UnitPrice * UnitsInStock AS StockValue
FROM Products;
ProductName UnitPrice UnitsInStock StockValue
Chai 18.00 39 702.00
Chang 19.00 17 323.00
Aniseed Syrup 10.00 13 130.00
Chef Anton's Cajun Seasoning 22.00 53 1166.00

Without AS StockValue, the calculated column would have no name at all; SSMS shows it as (No column name). Always give a calculation an alias.

The operators are the ones you know from C#: +, -, *, / and %. And as in C#, dividing one whole number by another drops the remainder: 7 / 2 is 3. Write 7 / 2.0 to keep the decimals.

A line total

Each row of OrderDetails is one product on one order. Its total is the price times the quantity:

SELECT OrderId
    , ProductId
    , UnitPrice
    , Quantity
    , UnitPrice * Quantity AS LineTotal
FROM OrderDetails;
OrderId ProductId UnitPrice Quantity LineTotal
10248 11 14.00 12 168.00
10248 42 9.80 10 98.00
10248 72 34.80 5 174.00

Joining text

+ also joins text, as it does in C#:

SELECT FirstName + ' ' + LastName AS EmployeeName
    , Title
FROM Employees;
EmployeeName Title
Nancy Davolio Sales Representative
Andrew Fuller Vice President, Sales

Text in SQL goes in single quotes: ' ' is a space. (Double quotes mean something else in SQL, so avoid them for text.)

CONCAT joins text too, and it also handles numbers and empty values without errors:

SELECT CONCAT(ProductName, ' (', UnitsInStock, ' in stock)') AS Label
FROM Products;
Label
Chai (39 in stock)
Chang (17 in stock)

Check yourself

2 questions to try on the lesson page.

So what

Calculated columns let the database hand you finished numbers and labels, so your app shows them instead of working them out itself.

Key terms

Cheat sheets