SQL Academy · Lesson

Arithmetic and Operators

Add, subtract, multiply and divide in SQL.

Arithmetic and Operators is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Doing Math in SELECT

SQL is not just for fetching rows. You can compute values right inside a query using arithmetic operators.

In this lesson you will add, subtract, multiply and divide using columns and literals, give the results names, and understand how PostgreSQL decides the type of each result.

Imagine an order_items table with quantity and unit_price. We can compute a line total without touching application code.

SELECT quantity, unit_price, quantity * unit_price AS line_total
FROM order_items;

The Five Arithmetic Operators

PostgreSQL supports the usual operators:

+ addition
- subtraction
* multiplication
/ division
% modulo (remainder)

They work on numeric columns and literals just like in any programming language. The query below shows each one in action.

SELECT
  10 + 3  AS sum,
  10 - 3  AS difference,
  10 * 3  AS product,
  10 / 3  AS quotient,
  10 % 3  AS remainder;

Operator Precedence

SQL follows standard math precedence: *, / and % bind tighter than + and -.

Use parentheses to make intent explicit and to override the default order. The two expressions below produce very different results.

SELECT
  2 + 3 * 4        AS without_parens,  -- 14
  (2 + 3) * 4      AS with_parens;     -- 20

Computing a Discounted Price

A common real task: apply a percentage discount. Multiply the price by (1 - discount_rate).

Here discount_rate is stored as a fraction (0.15 = 15%). Parentheses guarantee the subtraction happens before the multiply.

SELECT
  product_id,
  price,
  discount_rate,
  price * (1 - discount_rate) AS final_price
FROM products;

Arithmetic Across Columns

Operands do not have to be literals. You can combine multiple columns in one expression.

Below we compute net profit per sale: revenue minus cost, scaled by quantity sold.

SELECT
  sale_id,
  (sale_price - cost) * quantity AS profit
FROM sales;

Naming Results with AS

Computed columns get auto-generated names like ?column? unless you alias them. Always use AS to give readable names.

An alias also lets you reference the column more clearly in your tooling and reports.

SELECT
  base_salary,
  base_salary * 0.10 AS bonus,
  base_salary + base_salary * 0.10 AS total_pay
FROM employees;

Arithmetic in WHERE

You can use arithmetic in the WHERE clause too, not only in SELECT.

This filters to orders where the computed total exceeds 100. Note: wrapping a column in math can prevent index use, so prefer plain columns in filters when performance matters.

SELECT order_id, quantity, unit_price
FROM order_items
WHERE quantity * unit_price > 100;

Result Types Follow the Inputs

PostgreSQL infers the result type from the operands. Integer + integer gives an integer; if either side is numeric or float, the result is too.

This matters most with division, which we cover later. For now, notice how mixing types promotes the result.

SELECT
  pg_typeof(4 + 2)        AS int_type,      -- integer
  pg_typeof(4 + 2.0)      AS numeric_type,  -- numeric
  pg_typeof(4 * 1.5);                        -- numeric

The Modulo Operator

% returns the remainder of integer division. It is handy for cyclic logic, like "every Nth row" or checking even/odd values.

Below we find even product ids using % 2 = 0.

SELECT product_id, name
FROM products
WHERE product_id % 2 = 0;

Updating Values with Arithmetic

Arithmetic is not limited to SELECT. You can use it in UPDATE to adjust stored values.

This raises every price in a category by 5%. The expression on the right is evaluated per row.

UPDATE products
SET price = price * 1.05
WHERE category = 'electronics';

Watch Out for NULL

Any arithmetic involving NULL yields NULL, because the result is unknown.

If a column might be missing, wrap it with COALESCE to supply a default before doing math.

SELECT
  base + NULL                     AS is_null,      -- NULL
  base + COALESCE(bonus, 0)       AS safe_total
FROM payroll;

Quick Check

What does the expression 2 + 6 / 2 evaluate to in PostgreSQL?

Recap

You can compute values directly in SQL:

+ - * / % are the core operators
*, /, % bind tighter than + and -; use parentheses to be explicit
Alias computed columns with AS
Arithmetic works in SELECT, WHERE and UPDATE
Result type follows the operands, and any NULL operand makes the result NULL

Next we will tackle rounding and truncating those numeric results.

Frequently asked questions

Is the “Arithmetic and Operators” lesson free?

Yes — the full text of “Arithmetic and Operators” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Arithmetic and Operators”?

Add, subtract, multiply and divide in SQL. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Arithmetic and Operators” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy