How do I perform calculations in a SELECT statement?

By Wayan in Learning SQL 0 Comment

A SELECT statement is not limited to returning columns exactly as they are stored. You can compute new values on the fly — the total price of an order line, a discount, a tax amount, the age of a record, or the concatenation of a first and last name. These calculations happen inside the database and are returned as extra columns in your result set.

In this tutorial you will learn how to add computed columns to a SELECT statement using arithmetic operators, expressions on multiple columns, built-in functions, and aliases. You will also see how NULL affects the math, and which small differences to watch for between database systems.

Prerequisites

To follow along, you need:

  • A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
  • A client that can execute SQL statements.
  • Basic familiarity with the SELECT and WHERE clauses.

If you already followed the previous tutorials in this series, you can reuse the same sample tables.

Sample Database

We continue with the online bookstore schema used across the series so the examples stay consistent.

CREATE TABLE authors
(
    author_id   INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books
(
    book_id      INTEGER PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country)
VALUES (1, 'Jane Austen', 'United Kingdom'),
       (2, 'Haruki Murakami', 'Japan'),
       (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
       (4, 'Gabriel Garcia Marquez', 'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock,
                   published_on)
VALUES (1, 'Pride and Prejudice', 1, 'Classic', 12.50, 20, '1813-01-28'),
       (2, 'Emma', 1, 'Classic', 10.00, 0, '1815-12-23'),
       (3, 'Norwegian Wood', 2, 'Fiction', 15.75, 12, '1987-09-04'),
       (4, 'Kafka on the Shore', 2, 'Fiction', 18.20, 5, '2002-09-12'),
       (5, 'Half of a Yellow Sun', 3, 'Historical', 16.00, 8, '2006-08-11'),
       (6, 'Americanah', 3, 'Contemporary', 14.50, 3, '2013-05-14'),
       (7, 'One Hundred Years of Solitude', 4, 'Classic', 22.00, 25,
        '1967-05-30'),
       (8, 'Love in the Time of Cholera', 4, 'Classic', 19.99, 0, '1985-09-05'),
       (9, 'Unknown Title', 2, NULL, 13.00, 4, NULL);

Row 9 intentionally has NULL in category and published_on. We will use it to see how missing values behave in calculations.

Basic Syntax

Any expression that returns a value can appear in the SELECT list, not just a column name. You can combine literal values, columns, arithmetic operators, and function calls, and you can give the result a name with an alias.

SELECT
    column_name,
    expression        AS alias_name,
    function(column)  AS alias_name
FROM table_name
WHERE condition;
  • expression — anything the database can evaluate, such as price * stock or UPPER(title).
  • AS alias_name — a readable label for the computed column. The AS keyword is optional in most databases but recommended for clarity.
  • The calculation is executed per row, using values from that row.

Arithmetic Operators

SQL supports the standard arithmetic operators:

Operator Meaning Example
+ Addition price + 1.00
- Subtraction stock - 1
* Multiplication price * stock
/ Division price / 2
% Modulo (remainder), most engines stock % 2

The modulo operator % is supported by PostgreSQL, MySQL, MariaDB, SQL Server, and SQLite. Oracle Database uses the MOD(x, y) function instead. MOD(x, y) is portable and works on every engine listed above.

Practical Example

The bookstore manager wants a report of the total value of each book’s stock — that is, price × stock for every row.

SELECT
    book_id,
    title,
    price,
    stock,
    price * stock AS inventory_value
FROM books
ORDER BY inventory_value DESC;

Reading the query in logical order:

  1. FROM books — start with every row in books.
  2. SELECT ... — for each row, compute price * stock and label it inventory_value.
  3. ORDER BY inventory_value DESC — sort so that the most valuable inventory appears first.

Expected Result

book_id title price stock inventory_value
7 One Hundred Years of Solitude 22.00 25 550.00
1 Pride and Prejudice 12.50 20 250.00
3 Norwegian Wood 15.75 12 189.00
5 Half of a Yellow Sun 16.00 8 128.00
4 Kafka on the Shore 18.20 5 91.00
9 Unknown Title 13.00 4 52.00
6 Americanah 14.50 3 43.50
2 Emma 10.00 0 0.00
8 Love in the Time of Cholera 19.99 0 0.00

The calculation is performed row by row; nothing is aggregated across rows yet. That is a topic for a later tutorial on GROUP BY.

Additional Examples

1. Applying a Discount

Show each book with a 10% discount applied to its price.

SELECT
    title,
    price                 AS original_price,
    price * 0.90          AS discounted_price
FROM books
ORDER BY title;

The literal 0.90 is a numeric value, and the multiplication returns a numeric result. If you prefer to express the discount as a subtraction, price - (price * 0.10) produces the same value.

2. Rounding a Computed Value

Computed columns often have more decimal places than you want to display. Use ROUND(expression, digits) to control precision.

SELECT
    title,
    price,
    ROUND(price * 0.90, 2) AS discounted_price
FROM books
ORDER BY discounted_price DESC;

ROUND is available in every major database, though the exact rounding rules (half-up vs. banker’s rounding) can differ slightly. Always check the documentation if the last digit matters for financial reporting.

3. Building a Derived Column from Multiple Columns

The following query builds a compact line item description by concatenating text and formatting the price.

-- PostgreSQL, Oracle Database, SQLite
SELECT
    title || ' - $' || price AS line_item
FROM books
ORDER BY title;

|| is the ANSI string-concatenation operator. It is supported by PostgreSQL, Oracle Database, and SQLite. It is also supported by MySQL and MariaDB when PIPES_AS_CONCAT SQL mode is enabled, but it is not the default.

Portable alternative that works across engines:

-- Portable across PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, SQLite
SELECT
    CONCAT(title, ' - $', price) AS line_item
FROM books
ORDER BY title;

SQL Server uses + for string concatenation, but CONCAT is preferred because it handles NULL values without turning the whole result into NULL.

4. Integer Division and Numeric Types

Division behaves differently for integer and decimal operands. In PostgreSQL, Oracle Database, and SQL Server, dividing two integers truncates the fractional part:

SELECT 7 / 2 AS integer_division;

Result: 3 on PostgreSQL, SQL Server, and Oracle Database. MySQL, MariaDB, and SQLite return 3.5 because they promote the result to a floating-point value.

To make the intent explicit and portable, cast one operand to a numeric type:

SELECT
    stock,
    CAST(stock AS DECIMAL(10, 2)) / 2 AS half_stock
FROM books
WHERE stock > 0;

5. Using Built-in Functions

Databases provide a rich set of functions. A few commonly used ones in a SELECT list:

SELECT
    UPPER(title)         AS title_upper,
    LOWER(category)      AS category_lower,
    LENGTH(title)        AS title_length,
    ABS(stock - 10)      AS distance_from_ten,
    ROUND(price, 0)      AS price_rounded
FROM books
ORDER BY title;
  • UPPER / LOWER change case.
  • LENGTH returns the number of characters (in most databases; SQL Server uses LEN).
  • ABS returns the absolute value.
  • ROUND rounds a numeric value.

Each database ships its own set of scalar functions; consult your documentation for the full list.

6. Date Arithmetic

You can also compute values from date columns. The exact syntax depends on the database.

-- PostgreSQL: current date minus stored date returns an INTERVAL
SELECT
    title,
    published_on,
    CURRENT_DATE - published_on AS days_in_print
FROM books
WHERE published_on IS NOT NULL
ORDER BY days_in_print DESC;

-- MySQL / MariaDB
SELECT
    title,
    published_on,
    DATEDIFF(CURRENT_DATE, published_on) AS days_in_print
FROM books
WHERE published_on IS NOT NULL
ORDER BY days_in_print DESC;

-- SQL Server
SELECT
    title,
    published_on,
    DATEDIFF(DAY, published_on, CAST(GETDATE() AS DATE)) AS days_in_print
FROM books
WHERE published_on IS NOT NULL
ORDER BY days_in_print DESC;

Date functions vary substantially between databases. When portability matters, isolate them behind a helper view or in the application layer.

7. Using a Calculated Column in ORDER BY

Most databases accept an alias defined in the SELECT list inside the ORDER BY clause, because ORDER BY is logically evaluated after SELECT.

SELECT
    title,
    price,
    stock,
    price * stock AS inventory_value
FROM books
ORDER BY inventory_value DESC;

You can also repeat the expression instead of the alias — that always works, at the cost of some duplication.

8. Calculations in WHERE

You can filter by a computed value, but the expression must appear in the WHERE clause, not the alias, because WHERE is evaluated before SELECT.

Correct:

SELECT
    title,
    price * stock AS inventory_value
FROM books
WHERE price * stock > 100
ORDER BY inventory_value DESC;

Incorrect on most databases:

SELECT
    title,
    price * stock AS inventory_value
FROM books
WHERE inventory_value > 100;   -- error: alias not visible in WHERE

9. Calculations in Application Code

When your application reads a computed column, treat it the same as any other value. Use a PreparedStatement and parameters for any user-supplied inputs.

String sql = """
        SELECT
            book_id,
            title,
            price,
            stock,
            price * stock AS inventory_value
        FROM books
        WHERE category = ?
        ORDER BY inventory_value DESC
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, category);

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long bookId = resultSet.getLong("book_id");
            String title = resultSet.getString("title");
            BigDecimal price = resultSet.getBigDecimal("price");
            int stock = resultSet.getInt("stock");
            BigDecimal inventoryValue = resultSet.getBigDecimal("inventory_value");
            // process the row...
        }
    }
}

Use BigDecimal for monetary values to avoid the precision issues of binary floating point.

Common Mistakes

Forgetting That NULL Propagates Through Arithmetic

Any arithmetic expression that involves NULL returns NULL. If a nullable column participates in a calculation, expect NULL outputs.

Incorrect assumption:

SELECT
    title,
    price + NULL AS adjusted_price
FROM books;

Every row returns NULL for adjusted_price. To provide a default value, use COALESCE:

SELECT
    title,
    price + COALESCE(discount, 0) AS adjusted_price
FROM books;

COALESCE(expr1, expr2, ...) returns the first non-NULL argument and is supported by every major database.

Dividing by Zero

Dividing by zero raises an error in most databases (PostgreSQL, MySQL in strict mode, Oracle Database, SQL Server). Guard against it with a CASE expression or NULLIF.

SELECT
    title,
    price,
    stock,
    CASE WHEN stock = 0 THEN NULL
         ELSE price / stock
    END AS price_per_unit
FROM books;

Or, more compactly:

SELECT
    title,
    price / NULLIF(stock, 0) AS price_per_unit
FROM books;

NULLIF(a, b) returns NULL when a = b, and returns a otherwise. The result of the division becomes NULL instead of an error.

Referring to an Alias in WHERE

As shown earlier, aliases defined in the SELECT list are usually not visible in WHERE. Repeat the expression or wrap the query in a subquery / common table expression if the calculation is complex.

Assuming Integer Division Behaves Like Decimal Division

7 / 2 may return 3 on some databases and 3.5 on others. When the fractional part matters, cast at least one operand to a decimal or floating-point type.

Losing Precision with Floating-Point Types

FLOAT and DOUBLE PRECISION are approximate types. Use DECIMAL / NUMERIC for money, invoice totals, tax rates, and anything else where exact arithmetic is required.

Database Compatibility

Basic arithmetic (+, -, *, /), most standard scalar functions (ROUND, ABS, UPPER, LOWER, COALESCE, NULLIF, CAST), and column aliases are part of ANSI SQL and are supported by:

  • PostgreSQL
  • MySQL
  • MariaDB
  • SQL Server
  • Oracle Database
  • SQLite

Notable differences to be aware of:

  • String concatenation. || in PostgreSQL, Oracle Database, and SQLite. + in SQL Server. CONCAT(...) works everywhere.
  • Modulo. % works in PostgreSQL, MySQL, MariaDB, SQL Server, SQLite. Oracle Database uses MOD(x, y). MOD(x, y) is portable.
  • Integer division. PostgreSQL, Oracle Database, and SQL Server truncate on integer operands. MySQL, MariaDB, and SQLite return a floating-point result. Cast explicitly for portability.
  • String length. LENGTH on PostgreSQL, MySQL, MariaDB, SQLite, and Oracle Database. LEN on SQL Server. Oracle Database also has LENGTHB for byte length.
  • Date arithmetic. Every engine uses different function names (DATEDIFF, DATE_ADD, AGE, INTERVAL, etc.). Consult the documentation for your database and version.
  • Rounding rules. Different engines may implement half-up, half-even, or truncation for ROUND. Verify with your own test cases before using it for financial output.

When in doubt, consult the documentation for your database and version.

Best Practices

  • Always give computed columns an alias. Without one, the column name in the result set is engine-defined and unstable.
  • Use DECIMAL / NUMERIC for money. Binary floating-point types introduce rounding errors that are unacceptable in financial calculations.
  • Guard against division by zero with NULLIF or CASE.
  • Handle NULL explicitly with COALESCE when a nullable column participates in arithmetic.
  • Prefer portable functions (CONCAT, COALESCE, CAST, MOD) over engine-specific operators when the query may run on multiple databases.
  • Repeat expressions instead of relying on aliases in WHERE. Alternatively, use a common table expression or subquery so the calculation appears only once.
  • Use parameterized queries when constants in the calculation come from application input.
  • Avoid computing values in the application when the database can do it. The database can often use indexes and streaming, and less data is sent over the network.

Conclusion

You learned how to perform calculations in a SQL SELECT statement using arithmetic operators, string and date expressions, built-in functions, and aliases. You saw how NULL and division by zero require care, and how the same calculation can behave differently across database engines. Always use DECIMAL for money, guard against NULL and zero, and give every computed column a clear alias. Next, learn how to summarize rows with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX.