# List of expressions

For an introduction to expressions, check out Writing expressions in the notebook editor.

- Aggregations
- Functions
- abs
- between
- case
- ceil
- coalesce
- concat
- contains
- convertTimezone
- datetimeAdd
- datetimeDiff
- datetimeSubtract
- day
- endswith
- exp
- floor
- hour
- interval
- isempty
- isnull
- lefttrim
- length
- log
- lower
- minute
- month
- power
- quarter
- regexextract
- replace
- righttrim
- round
- second
- sqrt
- startswith
- substring
- trim
- upper
- week
- year

- Database limitations

## Aggregations

Aggregation expressions take into account all values in a field. They can only be used in the **Summarize** section of the notebook editor.

### Average

Returns the average of the values in the column.

Syntax: `Average(column)`

Example: `Average([Quantity])`

would return the mean for the `Quantity`

field.

### Count

Returns the count of rows (also known as records) in the selected data.

Syntax: `Count`

Example: `Count`

If a table or result returns 10 rows, `Count`

will return `10`

.

### CountIf

Only counts rows where the condition is true.

Syntax: `CountIf(condition)`

.

Example: `CountIf([Subtotal] > 100)`

would return the number of rows where the subtotal were greater than 100.

### CumulativeCount

The additive total of rows across a breakout.

Syntax: `CumulativeCount`

.

Example: `CumulativeCount`

.

### CumulativeSum

The rolling sum of a column across a breakout.

Syntax: `CumulativeSum(column)`

.

Example: `CumulativeSum([Subtotal])`

.

### Distinct

The number of distinct values in this column.

Syntax: `Distinct(column)`

.

`Distinct([Last Name])`

. Returns the count of unique last names in the column. Duplicates (of the last name “Smith” for example) are not counted.

### Max

Returns the largest value found in the column.

Syntax: `Max(column)`

.

Example: `Max([Age])`

would return the oldest age found across all values in the `Age`

column.

Related: Min, Average, Median.

### Median

Returns the median value of the specified column.

Syntax: `Median(column)`

.

Example: `Median([Age])`

would find the midpoint age where half of the ages are older, and half of the ages are younger.

Databases that don’t support `median`

: SQLite, Vertica, SQL server, MySQL. Presto only provides approximate results.

### Min

Returns the smallest value found in the column.

Syntax: `Min(column)`

.

Example: `Min([Salary])`

would find the lowest salary among all salaries in the `Salary`

column.

Related: Max, Median, Average.

### Percentile

Returns the value of the column at the percentile value.

Syntax: `Percentile(column, percentile-value)`

Example: `Percentile([Score], 0.9)`

would return the value at the 90th percentile for all values in that column.

Databases that don’t support `percentile`

: H2, MySQL, SQL Server, SQLite, Vertica. Presto only provides approximate results.

### Share

Returns the percent of rows in the data that match the condition, as a decimal.

Syntax: `Share(condition)`

Example: `Share([Color] = "Blue")`

would return the number of rows with the `Color`

field set to `Blue`

, divided by the total number of rows.

### StandardDeviation

Calculates the standard deviation of the column, which is a measure of the variation in a set of values. Low standard deviation indicates values cluster around the mean, whereas a high standard deviation means the values are spread out over a wide range.

Syntax: `StandardDeviation(column)`

Example: `StandardDeviation([Population])`

would return the SD for the values in the `Population`

column.

### Sum

Adds up all the values of the column.

Syntax: `Sum(column)`

Example: `Sum([Subtotal])`

would add up all the values in the `Subtotal`

column.

### SumIf

Sums up the specified column only for rows where the condition is true.

Syntax: `SumIf(column, condition)`

.

Example:`SumIf([Subtotal], [Order Status] = "Valid")`

would add up all the subtotals for orders with a status of “Valid”.

### Variance

Returns the numeric variance for a given column.

Syntax: `Variance(column)`

Example: `Variance([Temperature])`

will return a measure of the dispersion from the mean temperature for all temps in that column.

Related: StandardDeviation, Average.

## Functions

Function expressions apply to each individual value. They can be used to alter or filter values in a column, or create new, custom columns.

### abs

Returns the absolute (positive) value of the specified column.

Syntax: `abs(column)`

Example: `abs([Debt])`

. If `Debt`

were -100, `abs(-100)`

would return `100`

.

### between

Checks a date or number column’s values to see if they’re within the specified range.

Syntax: `between(column, start, end)`

Example: `between([Created At], "2019-01-01", "2020-12-31")`

would return rows where `Created At`

date fell within the range of January 1, 2019 and December 31, 2020.

Related: interval.

### case

Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.

Syntax: `case(condition, output, …)`

Example: `case([Weight] > 200, "Large", [Weight] > 150, "Medium", "Small")`

If a `Weight`

is 250, the expression would return “Large”. In this case, the default value is “Small”, so any `Weight`

150 or less would return “Small”.

### ceil

Rounds a decimal up (ceil as in ceiling).

Syntax: `ceil(column)`

.

Example: `ceil([Price])`

. `ceil(2.99)`

would return 3.

### coalesce

Looks at the values in each argument in order and returns the first non-null value for each row.

Syntax: `coalesce(value1, value2, …)`

Example: `coalesce([Comments], [Notes], "No comments")`

. If both the `Comments`

and `Notes`

columns are null for that row, the expression will return the string “No comments”.

### concat

Combine two or more strings together.

Syntax: `concat(value1, value2, …)`

Example: `concat([Last Name], ", ", [First Name])`

would produce a string of the format “Last Name, First Name”, like “Palazzo, Enrico”.

### contains

Checks to see if string1 contains string2 within it.

Syntax: `contains(string1, string2)`

Example: `contains([Status], "Class")`

. If `Status`

were “Classified”, the expression would return `true`

.

Related: regexextract.

### convertTimezone

Shifts a date or timestamp value into a specified time zone.

Syntax: `convertTimezone(column, target, source)`

.

Example: `convertTimezone("2022-12-28T12:00:00", "Canada/Pacific", "Canada/Eastern")`

would return the value `2022-12-28T09:00:00`

, displayed as `December 28, 2022, 9:00 AM`

.

### datetimeAdd

Adds some unit of time to a date or timestamp value.

Syntax: `datetimeAdd(column, amount, unit)`

.

Example: `datetimeAdd("2021-03-25", 1, "month")`

would return the value `2021-04-25`

, displayed as `April 25, 2021`

.

Related: between, datetimeSubtract.

### datetimeDiff

Returns the difference between two datetimes in some unit of time. For example, `datetimeDiff(d1, d2, "day") `

will return the number of days between `d1`

and `d2`

.

Syntax: `datetimeDiff(datetime1, datetime2, unit)`

.

Example: `datetimeDiff("2022-02-01", "2022-03-01", "month")`

would return `1`

.

### datetimeSubtract

Subtracts some unit of time from a date or timestamp value.

Syntax: `datetimeSubtract(column, amount, unit)`

.

Example: `datetimeSubtract("2021-03-25", 1, "month")`

would return the value `2021-02-25`

, displayed as `February 25, 2021`

.

Related: between, datetimeAdd.

### day

Takes a datetime and returns the day of the month as an integer.

Syntax: `day([datetime column])`

.

Example: `day("2021-03-25T12:52:37")`

would return the day as an integer, `25`

.

### endswith

Returns true if the end of the text matches the comparison text.

Syntax: `endsWith(text, comparison)`

`endsWith([Appetite], "hungry")`

Related: contains and startswith.

### exp

Returns Euler’s number, e, raised to the power of the supplied number. (Euler sounds like “Oy-ler”).

Syntax: `exp(column)`

.

Example: `exp([Interest Months])`

Related: power.

### floor

Rounds a decimal number down.

Syntax: `floor(column)`

Example: `floor([Price])`

. If the `Price`

were 1.99, the expression would return 1.

### hour

Takes a datetime and returns the hour as an integer (0-23).

Syntax: `hour([datetime column])`

.

Example: `hour("2021-03-25T12:52:37")`

would return `12`

.

### interval

Checks a date column’s values to see if they’re within the relative range.

Syntax: `interval(column, number, text)`

.

Example: `interval([Created At], -1, "month")`

.

Related: between.

### isempty

Returns true if the column is empty.

Syntax: `isempty(column)`

Example: `isempty([Discount])`

would return true if there were no value in the discount field.

### isnull

Returns true if the column is null.

Syntax: `isnull(column)`

Example: `isnull([Tax])`

would return true if no value were present in the column for that row.

### lefttrim

Removes leading whitespace from a string of text.

Syntax: `ltrim(text)`

Example: `ltrim([Comment])`

. If the comment were “ I’d prefer not to”, `ltrim`

would return “I’d prefer not to”.

### length

Returns the number of characters in text.

Syntax: `length(text)`

Example: `length([Comment])`

If the `comment`

were “wizard”, `length`

would return 6 (“wizard” has six characters).

### log

Returns the base 10 log of the number.

Syntax: `log(column)`

.

Example: `log([Value])`

.

### lower

Returns the string of text in all lower case.

Syntax: `lower(text)`

.

Example: `lower([Status])`

. If the `Status`

were “QUIET”, the expression would return “quiet”.

Related: upper.

### minute

Takes a datetime and returns the minute as an integer (0-59).

Syntax: `minute([datetime column])`

.

Example: `minute("2021-03-25T12:52:37")`

would return `52`

.

### month

Takes a datetime and returns the month number (1-12) as an integer.

Syntax: `month([datetime column])`

.

Example: `month("2021-03-25T12:52:37")`

would return the month as an integer, `3`

.

### power

Raises a number to the power of the exponent value.

Syntax: `power(column, exponent)`

.

Example: `power([Length], 2)`

. If the length were `3`

, the expression would return `9`

(3 to the second power is 3*3).

Databases that don’t support `power`

: SQLite.

Related: exp.

### quarter

Takes a datetime and returns the number of the quarter in a year (1-4) as an integer.

Syntax: `quarter([datetime column])`

.

Example: `quarter("2021-03-25T12:52:37")`

would return `1`

for the first quarter.

### regexextract

Extracts matching substrings according to a regular expression.

Syntax: `regexextract(text, regular_expression)`

.

Example: `regexextract([Address], "[0-9]+")`

.

Databases that don’t support `regexextract`

: H2, SQL Server, SQLite.

### replace

Replaces a part of the input text with new text.

Syntax: `replace(text, find, replace)`

.

Example: `replace([Title], "Enormous", "Gigantic")`

.

### righttrim

Removes trailing whitespace from a string of text.

Syntax: `rtrim(text)`

Example: `rtrim([Comment])`

. If the comment were “Fear is the mindkiller. “, the expression would return “Fear is the mindkiller.”

### round

Rounds a decimal number either up or down to the nearest integer value.

Syntax: `round(column)`

.

Example: `round([Temperature])`

. If the temp were `13.5`

degrees centigrade, the expression would return `14`

.

### second

Takes a datetime and returns the number of seconds in the minute (0-59) as an integer.

Syntax: `second([datetime column)`

.

Example: `second("2021-03-25T12:52:37")`

would return the integer `37`

.

### sqrt

Returns the square root of a value.

Syntax: `sqrt(column)`

.

Example: `sqrt([Hypotenuse])`

.

Databases that don’t support `sqrt`

: SQLite.

Related: Power.

### startswith

Returns true if the beginning of the text matches the comparison text.

Syntax: `startsWith(text, comparison)`

.

Example: `startsWith([Course Name], "Computer Science")`

would return true for course names that began with “Computer Science”, like “Computer Science 101: An introduction”.

### substring

Returns a portion of the supplied text, specified by a starting position and a length.

Syntax: `substring(text, position, length)`

Example: `substring([Title], 0, 10)`

returns the first 11 letters of a string (the string index starts at position 0).

Related: regexextract, replace.

### trim

Removes leading and trailing whitespace from a string of text.

Syntax: `trim(text)`

Example: `trim([Comment])`

will remove any whitespace characters on either side of a comment.

### upper

Returns the text in all upper case.

Syntax: `upper(text)`

.

Example: `upper([Status])`

. If status were “hyper”, `upper("hyper")`

would return “HYPER”.

### week

Takes a datetime and returns the week as an integer.

Syntax: `week(column, mode)`

.

Example: `week("2021-03-25T12:52:37")`

would return the week as an integer, `12`

.

- column: the name of the column of the date or datetime value.
- mode: Optional.
- ISO: (default) Week 1 starts on the Monday before the first Thursday of January.
- US: Week 1 starts on Jan 1. All other weeks start on Sunday.
- Instance: Week 1 starts on Jan 1. All other weeks start on the day defined in your Metabase localization settings.

### year

Takes a datetime and returns the year as an integer.

Syntax: `year([datetime column])`

.

Example: `year("2021-03-25T12:52:37")`

would return the year 2021 as an integer, `2,021`

.

## Database limitations

Limitations are noted for each aggregation and function above, and here there are in summary:

**H2**: `Median`

, `Percentile`

and `regexextract`

**MySQL/MariaDB**: `Median`

, `Percentile`

.

**SQL Server**: `Median`

, `Percentile`

and `regexextract`

**SQLite**: `log`

, `Median`

, `Percentile`

, `power`

, `regexextract`

, `StandardDeviation`

, `sqrt`

and `Variance`

**Vertica**: `Median`

and `Percentile`

Additionally, **Presto** only provides *approximate* results for `Median`

and `Percentile`

.

If you’re using or maintaining a third-party database driver, please refer to the wiki to see how your driver might be impacted.

See Custom expressions in the notebook editor to learn more.