Calculated Columns in SQL
Columns don't have to come straight from a table. You can calculate new ones as your query runs.
You have already met a calculated column: the margin in Lesson 2.2 was
price - cost, worked out as the query ran. SQL can calculate any
column this way. Any arithmetic you put in the
SELECT
list becomes part of the result.
A calculated column starts out unnamed, which makes for an ugly header. Give
it a clear name with
AS, the same aliasing you have already used to name counts and margins.
This lesson calculates a value in a query result; it does not add a column
to the table. Database documentation may call a column stored in the table
definition a generated, computed, or virtual
column. That is a schema feature with engine-specific syntax, covered in
generated columns. A SELECT expression is the portable choice when you only
need the value in a report.
select item_name,
price,
price * 1.06 as price_with_tax
from menuOpen the DuckDB playground with the matching dataset and query already filled in.
Schema · Garden Shop Table · products 9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
How much money is sitting on the shelves? For every product, show its quantity on hand, its cost,
and its stock value (quantity on hand times cost). Name the
calculated column stock_value and sort the most valuable stock
first, breaking ties by product name.
Run your query to inspect the result.
Expected answer
- Columns: product_name, quantity_on_hand, cost, stock_value.
- Rows: 24 products.
- stock_value is quantity times cost, so Monstera Deliciosa (40 × 15) leads at 600.
Related
Searched and simple CASE patterns.
Searched and simple CASE, plus conditional aggregation.
Practice WITH clauses and step-by-step reports.
WHERE is evaluated before SELECT aliases exist.
The WITH clause, chained steps, and CTE vs subquery.
Scalar, IN, and EXISTS subqueries plus WITH.