Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 125 ยท Functions and Expressions

Calculated Columns

Build new columns in SQL from existing ones. Compute totals, taxes and percentages, join text, avoid the integer division trap and handle NULL and floating-point results.

A table stores facts: a price, a quantity, a first name. Most questions need values that aren't stored anywhere: the total of an order line, a price with tax, an employee's monthly salary, a customer's full name. Instead of saving those in the table (where they could get out of date), you compute them in the query. A column that's computed from other values when the query runs is a calculated column (also called a derived or computed column).

You've seen the building blocks in SQL Operators. This lesson turns them into a practical skill: writing calculated columns that are correct. That means knowing when SQLite silently drops the fraction of a division, how NULL spreads through a calculation, and why 0.1 + 0.2 isn't exactly 0.3. The rest of this chapter then adds functions for text, numbers and dates on top.

Computing new columns

Any expression in the SELECT list becomes a column of the result. Give each calculated column a clear name with AS (see Column and Table Aliases):

SQL Price with tax and stock value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

For each row, the database takes that row's price and stock and evaluates the expressions. The Ultralight Tent costs 189, and its 71 units in stock are worth 13,419. With 20% VAT it should cost 226.8, but the result shows 226.79999999999998: a tiny floating-point error that you'll learn to handle later in this lesson. The table doesn't change; the calculated columns exist only in this result.

You can mix columns and constants freely and use the full set of operators, including parentheses:

SQL Order line totals ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Order 4 has two lines: one laptop stand and two yoga mats. (The 15% discount on the stand comes out as 28.474999999999998, another floating-point artifact.) Each calculated column is computed independently for every row, using only that row's values. Combining values across rows (the total of the whole order) is a different job, done by aggregate functions such as sum(): see Aggregate Functions.

Using a calculated column elsewhere in the query

You can sort by a calculated column through its alias, as the first example did with ORDER BY stock_value. To filter on it, repeat the expression in WHERE, since standard SQL doesn't let WHERE see aliases:

SQL
SELECT name, price * stock AS stock_value
FROM products
WHERE price * stock > 5000      -- repeat the expression here
ORDER BY stock_value DESC;      -- the alias is fine here

When an expression is long or used several times, compute it once in a subquery or a CTE (Common Table Expressions) and refer to it by name in the outer query.

The integer division trap

This is the most important rule in this lesson. In SQLite, an integer divided by an integer is an integer: the fraction is thrown away (truncated toward zero). PostgreSQL and SQL Server behave the same way. Salaries in the company database are stored as whole numbers, so dividing by 12 to get a monthly salary loses the cents:

SQL Integer versus real division ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The CEO's 210,000 divides evenly into 17,500, so the problem is invisible on that row. But Yara's 157,000 / 12 shows 13083 in the integer column and 13083.333333333334 in the others. Writing 12.0 instead of 12, or multiplying by 1.0 first, makes one operand real, so the whole division is real.

The damage is much worse for percentages and ratios, where the true result is between 0 and 1. Here's the share of a 40-hour week that each person spends on a project:

SQL Percentages done right ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

share_wrong is 0 for every row: 10 / 40 in integer division is 0. Multiplying by 100 first helps (1000 / 40 = 25), but still truncates a value like 12.5 down to 12. Only the real division gives exact percentages: 12.5, 37.5, 75.

Order matters in integer arithmetic. hours * 100 / 40 and hours / 40 * 100 look the same on paper, but the second one divides first: 10 / 40 is 0, and 0 * 100 is 0. When dividing, make sure at least one value is real (100.0), and you won't need to think about the order.

MySQL is different: its / operator always returns a decimal result (7 / 2 is 3.5000), and it offers DIV for integer division. That's a common source of bugs when moving queries between databases.

Database 7 / 2 Integer division Real division
SQLite 3 7 / 2 7 / 2.0, 7 * 1.0 / 2
PostgreSQL 3 7 / 2, div(7, 2) 7 / 2.0, 7::numeric / 2
SQL Server 3 7 / 2 7 / 2.0, CAST(7 AS decimal(10, 2)) / 2
MySQL / MariaDB 3.5000 7 DIV 2 7 / 2
Oracle 3.5 TRUNC(7 / 2) 7 / 2

What type is the result?

SQLite has a handy function, typeof(), that tells you the type of any value. It shows how types combine in arithmetic: integer with integer gives an integer, and anything with a real number gives a real:

SQL Result types

Press Run (or Ctrl + Enter) to see the result here.

CAST(7 AS REAL) is the explicit way to convert a value before dividing. The last column shows something SQLite does that stricter databases don't: in arithmetic, text that looks like a number is converted to a number, so '3' + 4 is 7. Don't rely on it in real queries; Types and CAST explains these conversions and how other databases handle them.

Floating-point surprises

Real numbers (REAL in SQLite, float/double in other databases) are stored in binary, and most decimal fractions, like 0.1, can't be represented exactly. Usually you never notice. Sometimes you do:

SQL Floating-point rounding

Press Run (or Ctrl + Enter) to see the result here.

0.1 + 0.2 is 0.30000000000000004, so comparing it with = 0.3 is false. Rounding before comparing fixes it. For display, round(value, 2) keeps results tidy; you'll learn round() and friends in Numeric Functions.

For money, the safest approach is to avoid binary fractions altogether:

  • store amounts as integer cents (1999 instead of 19.99) and divide by 100 only for display, or
  • in PostgreSQL, MySQL and SQL Server, use an exact DECIMAL(10, 2) / NUMERIC column type.

SQLite has no exact decimal type, which is why many SQLite applications use integer cents. Data Types compares the options.

NULL in calculations

If any input of a calculation is NULL, the result is NULL. That's logical (an unknown budget divided by 12 is an unknown monthly budget), but it can quietly blank out values in a report:

SQL NULL spreads through calculations ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Quarterly Forecasting has no budget, so its monthly budget and label are both NULL. Projects without an end date get a NULL duration, while the two finished projects show their length in days (364 and 274). Notice the integer division again: 310,000 / 12 shows as 25833. (julianday() turns a date into a day number; date math is covered in Date and Time Functions.)

When a missing value should count as something specific, replace it explicitly with coalesce():

SQL
SELECT
  name,
  coalesce(budget, 0) / 12 AS monthly_budget,
  'Budget: ' || coalesce(budget, 'not set') AS label
FROM projects;

You'll study coalesce() and its relatives in COALESCE, IFNULL and NULLIF.

Text columns

Calculated columns aren't only for numbers. The concatenation operator || builds new text from existing columns and constants. It's the standard operator; MySQL uses CONCAT() and SQL Server + or CONCAT() instead (see SQL Operators):

SQL Building text ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The last column uses a function, upper(), inside the expression; functions and operators combine freely. The next lesson, String Functions, introduces many more.

Storing calculated values: generated columns

Calculated columns live only in a query's result. If you need the same expression in many queries, two options keep it in one place:

  • a view that includes the calculated column (see Views), or
  • a generated column in the table definition, which the database computes from other columns automatically:
SQL
-- SQLite 3.31+, PostgreSQL 12+, MySQL 5.7+ (SQL Server calls these "computed columns")
CREATE TABLE order_lines (
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  total_cents INTEGER GENERATED ALWAYS AS (quantity * unit_price_cents) STORED
);

Unlike a normal column, a generated column can never disagree with the values it's computed from. Constraints covers them in detail.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In SQLite, what does SELECT 45 / 60 * 100; return?

    Integer division happens first: 45 / 60 is 0, and 0 * 100 is 0. Use 45 * 100.0 / 60 to get 75.0.
  2. 2. Which expression gives the exact percentage of done out of total, both integer columns, in SQLite?

    Making one operand real (100.0) before dividing turns the division into real division. The other forms divide two integers and lose the fraction.
  3. 3. A project has budget = NULL. What is budget / 12 for that row?

    Any calculation with NULL produces NULL. Use coalesce(budget, 0) / 12 if a missing budget should count as 0.
  4. 4. Why is 0.1 + 0.2 = 0.3 false in SQLite?

    Binary floating point can't represent most decimal fractions exactly. Round before comparing, or store money as integer cents.

Summary

  • A calculated column is an expression in the SELECT list, computed for every row when the query runs; name it with AS.
  • Calculations use one row's values; combining rows needs aggregate functions.
  • Integer / integer is integer division in SQLite, PostgreSQL and SQL Server. Write 100.0, * 1.0 or CAST(x AS REAL) for real division; MySQL's / is always real.
  • typeof() shows the type of a value in SQLite.
  • Floating-point results can be slightly off (0.1 + 0.2); round for display and store money as integer cents or DECIMAL.
  • NULL anywhere in a calculation makes the result NULL; use coalesce() to supply a default.
  • Build text with ||; reuse calculations with views or generated columns.

Next: String Functions, where you'll measure, cut, search and reshape text.