Computed columns

Add columns written as Excel formulas: running totals, margins, changes from the row before, and dates.

The column: directive adds a column to the result, worked out with an Excel formula from the result's own columns. The formula language is Excel's: the same operators, the same precedence, the same functions with the same arguments, and the same error values. The one difference is how you point at a cell: a result has named columns instead of lettered ones, so a reference names the column with !.

-- sqlly column: Line Total = !Qty * !Price
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
SELECT OrderId, Qty, Price, Revenue, Cost FROM order_lines ORDER BY OrderId;

The database never sees the formula. SQLly computes the column as rows arrive, and an Excel export writes it as a live Excel formula. You don't have to type the directive: right-click a column header and choose Add Computed Column…, and SQLly writes the line for you. The Computed columns guide walks through the formula bar.

Syntax

-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]

A ; inside a quoted string or inside parentheses belongs to the formula, so TEXTJOIN("; ", TRUE, !A, !B) works as written. A later column: line with the same name replaces the earlier one. A column the result already has is never replaced: if the query returns a Total, a computed Total is skipped for that result.

References

Rows are numbered in the order the query returned them, so add an ORDER BY whenever a formula reads another row. Column names are matched without regard to case.

• Before the first row is blank. !Qty[1] on the first row reads an empty cell, which counts as 0 in arithmetic and "" in text. The same goes for !Qty[-1] on the last row.
• A column whose formula reads a column the result doesn't have is left off that result, so one directive can serve a batch whose result sets have different columns.
• Ranges go inside functions. SUM(!Qty[2]:!Qty) is fine; a bare !Qty[2]:!Qty as the whole formula is #VALUE!, as in Excel.

Variables

@name reads a value the query declares, so one number can drive both the SQL and the formulas:

-- sqlly set rate = 0.0825
-- sqlly param: @region text = West
-- sqlly column: Tax = ROUND(!Amount * @rate, 2)
-- sqlly column: Label = @region & " - " & !Customer
SELECT Customer, Amount FROM invoices WHERE Region = @region;

• -- sqlly set values read as numbers when they look like one, and as text otherwise.
• -- sqlly param: values keep their declared type: int and decimal as numbers, bool as TRUE or FALSE, date and datetime as real dates, everything else as text. The value typed in the parameter strip wins over the declared default, and null reads as blank.
• An undeclared variable gives #NAME?.

Operators

The same operators as Excel, from the loosest to the tightest binding:

Excel's quirks come along on purpose, so a formula gives the same answer in the grid and in the workbook: negation binds tighter than ^, so -2^2 is 4; operators of equal rank work left to right, so 2^3^2 is 64; and 1+2&3 is the text "33". Use parentheses when in doubt. Text that reads as a number is a number in arithmetic ("3"+4 is 7), and TRUE counts as 1.

Blanks, NULLs and errors

• A NULL is a blank cell. It counts as 0 in arithmetic and as "" in text, the way an empty cell does in Excel. ISBLANK() tells a blank apart from a zero.
• Errors are Excel's, and they flow through formulas the same way: #DIV/0! (division by zero), #VALUE! (the wrong kind of value, like "x"+1), #REF! (a column that isn't there), #NAME? (an unknown function or variable), #NUM! (a number out of range, like SQRT(-1)), and #N/A (no value, from NA() or an IFS with no match). An error shows in the cell; it never stops the query.
• Catch them as in Excel: IFERROR(!Profit / !Units, 0).

Functions

Every function below takes the same arguments in the same order as in Excel, and gives the same answer. A name SQLly doesn't know gives #NAME?.

Logical

IF, IFS, IFERROR, IFNA, AND, OR, NOT, XOR, SWITCH, CHOOSE, TRUE, FALSE, and COALESCE (see below).

Information

ISBLANK, ISNUMBER, ISTEXT, ISLOGICAL, ISERROR, ISERR, ISNA, ISEVEN, ISODD, N, T, NA.

Math and statistics

ABS, SIGN, ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, CEILING.MATH, FLOOR, FLOOR.MATH, EVEN, ODD, MOD, QUOTIENT, POWER, SQRT, EXP, LN, LOG, LOG10, PI, SIN, COS, TAN, DEGREES, RADIANS, SUM, PRODUCT, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, COUNTBLANK.

Text

LEFT, RIGHT, MID, LEN, UPPER, LOWER, PROPER, TRIM, CLEAN, CONCAT, CONCATENATE, TEXTJOIN, SUBSTITUTE, REPLACE, REPT, FIND, SEARCH, EXACT, TEXT, FIXED, VALUE, CHAR, CODE, TEXTBEFORE, TEXTAFTER.

Date and time

DATE, TIME, TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, EDATE, EOMONTH, DATEDIF, DAYS, DATEVALUE, TIMEVALUE, NETWORKDAYS, WORKDAY.

• COALESCE(a, b, …) is SQLly's one addition, borrowed from SQL: the first argument that is neither blank nor "", else the last one. COALESCE(!Nickname, !FirstName, "Customer"). In an Excel export it is written as nested IFs, since Excel has no such function.
• Dates work as in Excel. A date or time column from the database reads as a real date, so !ShippedOn - !OrderedOn is a number of days, !Due + 30 is a date, and EDATE, EOMONTH and DATEDIF behave as you expect.
• TODAY() and NOW() read your computer's clock when the query runs.
• TEXT takes Excel's format codes: "#,##0.00", "0.0%", "000", "$#,##0", "yyyy-mm-dd", "mmm d, yyyy", "dddd", "h:mm AM/PM", "hh:mm:ss".

Examples

Margin as a fraction of revenue, guarded against zero, placed after the Cost column. Give it -- sqlly format: column=Margin %; number=1; style=percent to show it as a percentage:

-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost

A running total. The column reads its own value on the row before; N() turns the blank above the first row into 0:

-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;

Change from the previous row, as an amount and as a percentage. The first row has nothing before it, so both show 0 there rather than the whole first value or a #DIV/0!:

-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)

Each month as a percentage of the first month:

-- sqlly column: vs First = !Sales / !Sales[$1]

A rolling sum and average of the last three rows:

-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)

Days between two dates, working days, and a due date at the end of next month:

-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)

Text built from numbers and dates with TEXT:

-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")

A fallback when a column is empty, and a tier label:

-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

How columns are computed

• In the order they are declared. A formula can read a computed column declared above it on the same row, and any computed column, itself included, on earlier rows. That is what makes a running total possible.
• In the order rows arrive. [1] always means the row the database sent before this one, not the row above it after you sort the grid. Sorting or filtering the grid moves the computed values with their rows; it doesn't recompute them.
• Each column gets a type from its formula. Arithmetic gives numbers; date functions and date arithmetic give dates (so date formats and date filters work on them); comparisons, AND, OR and the IS functions give TRUE/FALSE; text functions and & give text. An IF whose branches give different kinds is text, so IF(…, "", !A - !B) makes a text column; use 0 instead of "" to keep it numeric.
• Which result sets get the column. Every result set that has all the columns the formula reads, or only result n with result=n.
• Formatting works as on any other column. format:, conditional:, grid: and the pivot all treat a computed column like one the database returned.
• Very large results. A result too large to keep in memory is paged from disk. Formulas then see only the rows loaded with each page (plus a short look-back for [n] references), so a running total starts over with each page. For a long running total, let the database compute it with a window function, such as SUM(Amount) OVER (ORDER BY InvoiceDate).

The user guide, with the formula bar and colored references: Results › Computed columns. How computed columns become live formulas in a workbook: Excel export.