---
title: "Calculate new columns with Formula in Strata | Oplema"
description: "Add or update columns with SQL expressions using Formula in Strata. A formula can use the result of an earlier one, and you can set each output type."
url: "https://oplema.com/docs/strata/prep/formula"
updated: "2026-11-04"
---

# Formula

Calculate new values from existing columns, such as quantity times unit price, or update a column with an expression. Add formulas in order so later calculations can use earlier results.

**Learn this in context:** [Add a calculated column to a table](https://oplema.com/learn/export-to-report/add-a-calculated-column) (Build a report from a CSV export); [Label rows from a small lookup table](https://oplema.com/learn/many-sources/label-rows-from-a-lookup-table) (Combine and reconcile data files)

## Calculate item totals

Manual Input has four rows with quantity and unit\_price. Formula adds total as a DOUBLE using quantity \* unit\_price.

Result
| quantity | unit\_price | total |
| --- | --- | --- |
| 2 | 5 | 10 |
| 3 | 4 | 12 |
| 0 | 8 | 0 |
|  | 6 |  |

1. Select Add to create a second formula. Set Output column to `discounted_total`, Output type to `DOUBLE` and Expression to `total * 0.9`. This applies a 10% discount using the first formula's result.
2. The new column contains 9, 10.8, 0 and NULL. Change its Expression to `total * 0.8` to apply a 20% discount instead; the results become 8, 9.6, 0 and NULL.
3. Change the second formula's Output column to `total`. It now replaces `total` with the discounted value instead of adding another column.

## When to use

Use Formula to calculate a new column from existing ones, such as quantity times unit price. For totals across rows, use Summarize.

## Read the output

The output keeps `quantity` and `unit_price` and adds `total`. The first two rows produce `10` and `12`. A quantity of zero produces `0`; the missing quantity produces NULL. All four rows stay.

The steps add `discounted_total` after `total`, so it can use the calculated amount. Both columns are DOUBLE, so fractional discounts are kept.

## Replace an existing value

If an output name matches an existing column, the formula replaces that column. The expression can read the old value, so a second formula named `total` with `total * 0.9` discounts the total from the first formula. The input table doesn't change.

See the [functions reference](https://oplema.com/docs/strata/functions) for functions you can use in expressions.

To route rows using a condition, use [Filter](https://oplema.com/docs/strata/prep/filter).

To calculate totals across groups of rows, use [Summarize](https://oplema.com/docs/strata/transform/summarize).

## Inputs and outputs

### Inputs

1 port

**In Required**

Accepts: Tables, Geospatial data.

Accepts one connection on this port.

### Outputs

1 port

**Out**

## Configure the block

Add one formula per result and place dependent calculations after the formulas they use.

**Add**

Add a formula. Each entry creates or replaces one output column.

**Output column**

Name the result. A new name appends a column; an existing name replaces that column's values.

**Output type**

Choose VARCHAR for text, BIGINT or INTEGER for whole numbers, DOUBLE or FLOAT for fractional numbers, DECIMAL for exact numbers, BOOLEAN for true/false, DATE, TIME, TIMESTAMP or TIMESTAMP WITH TIME ZONE. Results that can't be converted to the selected type become NULL. Default: BIGINT.

**Decimal places**

Digits kept after the decimal point. Values round to this many places.

**Total digits**

Maximum digits in the value, including decimal places. Values that don't fit become NULL.

**Expression**

Enter a DuckDB SQL expression using input columns or earlier formula results. Enter only the expression, without a leading equals sign or a SELECT statement.

## Formula FAQs

**Can a formula use another formula's result?**

Yes. Formulas run in list order. A later formula can use a column created or replaced by an earlier one. Referencing a column that doesn't yet exist causes an error.

**What happens to missing values?**

It depends on the expression. In this example, multiplying a missing quantity by unit\_price produces NULL. Use coalesce(quantity, 0) \* unit\_price only if a missing quantity should count as zero.

**Does an invalid expression become NULL?**

No. Invalid SQL or an unknown column causes an error. A valid expression can still give NULL if its result can't be converted to the selected Output type.

**How do I write text values and column names?**

Use single quotes for text values. Put column names containing spaces in double quotes. Simple names such as quantity need no quotes.

### Prepare your data. Build it in Strata.

Strata opens in your browser, with no account.

[Open Strata](https://oplema.com/editor?path=%2F%3Fopen%3Dstrata) [About Strata](https://oplema.com/strata)
