Arithmetic in SELECT

Explanation

SQL isn't just for retrieving raw column values, you can also perform calculations right inside a SELECT statement. This is useful whenever you need a derived value that doesn't exist in the table itself, like an annual salary converted to a monthly figure, a price with tax added, or a discount applied to a total.

The basic arithmetic operators work the way you'd expect from any calculator:

You can apply these directly to column values, and combine them with numeric literals. The result becomes a new, computed column in your output, it's calculated fresh every time the query runs.

Syntax

SELECT column_name, expression AS alias_name
FROM table_name;

For example:

SELECT first_name, salary, salary / 12 AS monthly_salary
FROM employees;

You can also combine multiple operators in one expression, and use parentheses to control the order of operations, just like in regular math:

SELECT first_name, (salary + 5000) * 1.10 AS projected_salary
FROM employees;

Interactive Example

Try running these queries. Feel free to change the numbers and see how the computed columns change.

Common Mistakes

• Forgetting to alias the computed column. Without AS, the column header in your results becomes the raw expression text, which is hard to read and hard to reference later.
• Dividing integers and expecting a decimal. In some databases, dividing two whole numbers truncates the result to a whole number. If you need precision, multiply by a decimal first, for example salary / 12.0.
• Mixing up operator order. salary + 1000 * 1.1 does not add 1000 to the salary and then multiply, multiplication happens first. Use parentheses when in doubt: (salary + 1000) * 1.1.

Best Practices

• Always alias computed columns with a clear, descriptive name.
• Use parentheses generously to make the intended order of operations obvious, even when you don't strictly need them.
• Keep computed expressions simple and readable. If a calculation gets very complex, consider breaking it into steps or documenting what it represents.

Practice Question

Using the playground above, write a query that returns each employee's first_name, their salary, and a new column called salary_with_bonus that adds a flat $2,000 bonus to their salary.

Summary

Arithmetic expressions let you compute new values directly from existing columns, right inside a SELECT statement. Combine +, -, *, and / with parentheses to build the calculation you need, and always alias the result with AS so it's easy to read and reference.

FAQ