Building Clean Derived Columns

Learning outcomes

A derived column is calculated by the query rather than stored directly in the source table. A clean derived column has a stable meaning, name, type, unit, NULL policy, and calculation order.

A derived column is an interface

Start with an explicit calculation contract

Use descriptive aliases

SELECT    s.sale_id,    p.product_name,    p.unit_price * s.quantity AS gross_amount,    COALESCE(s.discount_rate, 0) AS effective_discount_rate,    p.unit_price * s.quantity * COALESCE(s.discount_rate, 0)        AS discount_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_idORDER BY s.sale_id;

Aliases should survive a code review without requiring readers to reconstruct the expression. Avoid reusing a source-column name for a value whose semantics changed.

Do not rely on a select-list alias in another select-list expression

SELECT    p.unit_price * s.quantity AS gross_amount,    gross_amount * COALESCE(s.discount_rate, 0) AS discount_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_id;

Most engines do not make one select-list alias available to a sibling expression. Layer the calculation in a subquery.

SELECT    sale_id,    gross_amount,    effective_discount_rate,    gross_amount * effective_discount_rate AS discount_amountFROM (    SELECT        s.sale_id,        p.unit_price * s.quantity AS gross_amount,        COALESCE(s.discount_rate, 0) AS effective_discount_rate    FROM sale AS s    JOIN product AS p ON p.product_id = s.product_id) AS priced_saleORDER BY sale_id;

Round at the presentation boundary

SELECT    sale_id,    ROUND(gross_amount, 2) AS gross_amount,    ROUND(discount_amount, 2) AS discount_amount,    ROUND(gross_amount - discount_amount, 2) AS net_amountFROM (    SELECT        s.sale_id,        p.unit_price * s.quantity AS gross_amount,        p.unit_price * s.quantity * COALESCE(s.discount_rate, 0)            AS discount_amount    FROM sale AS s    JOIN product AS p ON p.product_id = s.product_id) AS calculated_saleORDER BY sale_id;

Rounding each intermediate factor can produce a different total from calculating at full available precision and rounding the final monetary fields. The correct boundary is a business decision.

Expose assumptions rather than hiding them

SELECT    sale_id,    COALESCE(discount_rate, 0) AS effective_discount_rate,    discount_rate IS NULL AS discount_was_missingFROM saleORDER BY sale_id;

A consumer can use the effective rate while still distinguishing a recorded zero from a missing source value.

Keep units consistent

SELECT    sale_id,    COALESCE(discount_rate, 0) AS discount_ratio,    ROUND(COALESCE(discount_rate, 0) * 100, 1) AS discount_percentFROM saleORDER BY sale_id;

Name the unit in the alias when confusion is possible. A value of 0.15 as a ratio and 15.0 as a percentage are not interchangeable.

Derived labels should use controlled categories

SELECT    product_id,    product_name,    stock_qty,    CASE        WHEN stock_qty = 0 THEN 'out_of_stock'        WHEN stock_qty < 25 THEN 'low_stock'        ELSE 'available'    END AS stock_statusFROM productORDER BY product_id;

Machine-facing labels are often safer as stable codes such as low_stock. User-facing translation and wording can occur in the presentation layer.

Build one clean reporting projection

SELECT    sale_id,    sold_date,    customer_name,    product_label,    sales_channel,    quantity,    gross_amount,    discount_percent,    discount_amount,    net_amount,    discount_was_missingFROM (    SELECT        s.sale_id,        DATE(s.sold_at) AS sold_date,        TRIM(c.full_name) AS customer_name,        UPPER(p.category) || ': ' || p.product_name AS product_label,        s.sales_channel,        s.quantity,        ROUND(p.unit_price * s.quantity, 2) AS gross_amount,        ROUND(COALESCE(s.discount_rate, 0) * 100, 1)            AS discount_percent,        ROUND(            p.unit_price * s.quantity * COALESCE(s.discount_rate, 0),            2        ) AS discount_amount,        ROUND(            p.unit_price * s.quantity            * (1 - COALESCE(s.discount_rate, 0)),            2        ) AS net_amount,        s.discount_rate IS NULL AS discount_was_missing    FROM sale AS s    JOIN customer AS c ON c.customer_id = s.customer_id    JOIN product AS p ON p.product_id = s.product_id) AS sale_reportORDER BY sold_date, sale_id;

The outer query declares the published column order. The inner query groups transformations and calculations into one reviewable layer.

Verify values and types

SELECT    sale_id,    net_amount,    TYPEOF(net_amount) AS net_amount_type,    discount_was_missing,    TYPEOF(discount_was_missing) AS flag_typeFROM (    SELECT        s.sale_id,        ROUND(            p.unit_price * s.quantity            * (1 - COALESCE(s.discount_rate, 0)),            2        ) AS net_amount,        s.discount_rate IS NULL AS discount_was_missing    FROM sale AS s    JOIN product AS p ON p.product_id = s.product_id) AS checked_reportORDER BY sale_id;

Automated tests should verify representative values, NULL cases, boundary values, result types, and column names. SQLite boolean expressions return integer 0 or 1; other engines may return a boolean type.

Derived columns versus stored generated columns

Practice lab

1. Create clean customer name and email columns.
2. Create gross, discount, and net amounts.
3. Expose both the effective discount rate and a missingness flag.
4. Create stable product availability labels.
5. Wrap the final report in a subquery and publish an explicit output order.

SELECT    sale_id,    sold_date,    customer_name,    product_name,    gross_amount,    discount_amount,    net_amount,    discount_was_missingFROM (    SELECT        s.sale_id,        DATE(s.sold_at) AS sold_date,        TRIM(c.full_name) AS customer_name,        p.product_name,        ROUND(p.unit_price * s.quantity, 2) AS gross_amount,        ROUND(p.unit_price * s.quantity * COALESCE(s.discount_rate, 0), 2)            AS discount_amount,        ROUND(p.unit_price * s.quantity * (1 - COALESCE(s.discount_rate, 0)), 2)            AS net_amount,        s.discount_rate IS NULL AS discount_was_missing    FROM sale AS s    JOIN customer AS c ON c.customer_id = s.customer_id    JOIN product AS p ON p.product_id = s.product_id) AS report_rowsORDER BY sold_date, sale_id;

Review checklist

Common failures

Opaque aliases

Names such as calc1 force every consumer to rediscover the expression.

Same-level alias reuse

This is unsupported or behaves differently across engines.

Mixed units

Ratios, percentages, currency, and counts need explicit names.

Silent NULL replacement

A usable fallback can erase source-state information.

Premature rounding

Rounding intermediate values can change totals.

Copying the same business expression

Duplicate definitions drift; govern reusable logic deliberately.

Chapter 6 summary

• Scalar functions transform string, numeric, date/time, and typed values.
• COALESCE and NULLIF provide explicit missing-value policies.
• CASE creates conditional values according to ordered rules.
• Function portability requires testing names, syntax, types, and behavior.
• Derived columns are output contracts with names, units, types, NULL rules, and calculation boundaries.

Chapter 7 introduces joins and explains how rows from related tables are matched, preserved, multiplied, and debugged.

References

• SQLite: SELECT
• SQLite: Core Functions
• PostgreSQL: Value Expressions
• Microsoft Learn: Expressions