Level 3 · Compute margin per product

The SELECT list is not limited to existing columns. Any expression works: arithmetic, functions, or constants. Analysts do this constantly to derive metrics such as margin, tax or price per unit.

SELECT
  name,
  unit_price,
  unit_price * 1.08 AS price_with_tax
FROM products;

Aliases with AS

AS price_with_tax gives the computed column a name, called an alias. Without it the database invents a header such as (unit_price * 1.08), which is ugly in a spreadsheet and impossible to reference cleanly later. Use lowercase snake_case aliases that describe the metric. AS is optional in most databases (unit_price * 1.08 price_with_tax also works), but writing it makes the intent obvious and avoids the missing-comma bug from the previous level.

Rounding

ROUND(x, 2) rounds to two decimals. Round at the very end, on the final number. If you round an intermediate value and then divide it, the error compounds:

-- good: compute from raw values, round once
ROUND((unit_price - unit_cost) / unit_price * 100, 1) AS margin_pct

NULL in arithmetic

Some products have no unit_cost recorded. Any arithmetic with NULL returns NULL: 49.99 - NULL is NULL, not 49.99. That is usually what you want here: an unknown cost means an unknown margin, and reporting it as a 100% margin would be wrong. You will learn to replace NULLs deliberately with COALESCE in chapter 6, but only do it when a default really makes sense.

Integer division

In this dataset prices are decimals, so division behaves as expected. Be careful with integer columns though. In Postgres, 7 / 2 returns 3 because both sides are integers. DuckDB and MySQL return 3.5, and BigQuery requires DIV() for integer division. A safe portable habit when dividing counts is to multiply by 1.0 first, or cast: CAST(a AS DECIMAL) / b.

Common mistakes

• Forgetting parentheses: unit_price - unit_cost / unit_price divides first, then subtracts. Multiplication and division bind tighter than addition and subtraction, same as in math class.
• Referring to an alias in the same SELECT list: SELECT unit_price - unit_cost AS m, m / unit_price fails in Postgres, MySQL and BigQuery. DuckDB allows it, but you should repeat the expression or use a subquery so your SQL runs everywhere.