Computed columns

functional

Add a column worked out with an Excel formula from the result's own columns, with Excel's formula bar, colors, and functions.

If you can write an Excel formula, you can add a column to any query result in SQLly. A margin, a running total, the change from last month, the number of working days between two dates: type the formula you would type in Excel, and the new column appears next to the data. You don't need to know how to write it in SQL, and you don't need to run the query again.

It works like a calculated column in an Excel table: one formula for the whole column, worked out on every row. The only new thing to learn is how to point at a cell, because a result has column names instead of letters.

Add your first computed column

Run a query. Any result works; say one with Qty and Price columns.
Right-click the header of the column the new one should follow, and choose Add Computed Column….
The formula bar opens above the results: fx, a Name field, =, and the formula field. SQLly fills in a name (Computed) and starts the formula with a reference to the column you clicked, such as =!Price.
Name the column and type the formula, for example Line Total and =!Qty * !Price.
Press Enter or click Add Column. Esc or Cancel closes the bar without changing anything.

The formula bar above a result: each reference in the formula in its own color, and the cells it reads outlined in the grid in the same colors.

From A1 to !Name

Where Excel says B7, SQLly says !Qty: an exclamation mark and the column name. Everything else in the formula is plain Excel.

In Excel (on row 7)	In SQLly	Means
`=B7*C7`	`=!Qty * !Price`	This row's Qty times this row's Price.
`=B7-B6`	`=!Qty - !Qty[1]`	The change from the row before. `[2]` is two rows up.
`=B8`	`=!Qty[-1]`	The row after.
`=B7/B$2`	`=!Qty / !Qty[$1]`	Compared with the first row, which stays put on every row.
`=SUM(B5:B7)`	`=SUM(!Qty[2]:!Qty)`	This row and the two before it.
`=SUM(C7:E7)`	`=SUM(!Jan:!Mar)`	Across the columns from Jan to Mar.
`=B7*TaxRate`	`=!Qty * @rate`	A named value. `@rate` reads a `-- sqlly set` or `-- sqlly param:` value from the query.

Put a column name with spaces in double quotes: !"Order Total", or !"Order Total"[1] for the row before. Names ignore case, so !qty works too.

The functions are Excel's, with the same names, arguments and answers: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS and about a hundred more. So are the operators, including & for joining text and %. The reference page lists every function. One extra comes from SQL: COALESCE(a, b, …) gives the first value that isn't empty.

See what the formula reads

Excel colors each reference while you type a formula and draws a matching box around the cell. SQLly does the same:

Each reference gets its own color, in Excel's order: blue, red, purple, green, orange and so on. A reference you use twice keeps one color. The colored formula is shown under the field as you type.
The grid outlines the cells it reads, in the same colors, and tints those columns. The outlines are drawn for the row you have selected, or the first row if none is, so !Qty[1] visibly boxes the cell one row up.
Mistakes are spelled out under the field, such as "There is a ) without a matching (." or a hint to write a column as !Name, and Add Column waits until the formula reads correctly.

What happens when you apply it

The column is computed straight away from the rows already on screen. Nothing is sent to the database and nothing runs again, so it's instant even when the query was slow.

SQLly also writes the formula into your query as one comment line, at the top of the statements that ran:

-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;

That line is how the column comes back. Run the query tomorrow, save the .sql file, send it to a colleague: the column is there every time. The database ignores the comment, so the query still runs anywhere else. It's a single edit, so ⌘Z (CtrlZ on Windows and Linux) takes it back out of the query.

A result with two computed columns, Line Total and Running Total, next to the columns they read, and the sqlly column lines at the top of the query.

Once it's there, a computed column is a column like any other. Sort it, filter it, give it a number format, color it with conditional formatting, or use it in a pivot.

Read and edit a formula

Click any cell in a computed column and the formula bar shows its formula, read-only, with the same colors and outlines. It's the quickest way to check how a number was worked out.
Click Edit Formula in that bar, or right-click the column's header and choose Edit Formula…, to change it. The button now says Update Column.
Applying an edit replaces the column's line in the query rather than adding a second one, wherever it is, including inside a lock block. Rename the column in the Name field and the line is renamed with it.

A selected cell in a computed column, with its formula shown read-only in the formula bar, colored references, and the Edit Formula button.

Formulas worth stealing

Column	Formula
Margin %	`=IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue)`
Running total	`=!Amount + N(!"Running Total"[1])` (name the column Running Total)
Change from last row	`=IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])`
Growth %	`=IFERROR(!Sales / !Sales[1] - 1, 0)`
Indexed to the first month	`=!Sales / !Sales[$1]`
Rolling 3-month total	`=SUM(!Sales[2]:!Sales)`
Days to ship	`=!ShippedDate - !OrderDate`
Due at month end	`=EOMONTH(!InvoiceDate, 1)`
Days overdue	`=MAX(0, TODAY() - !DueDate)`
Readable label	`=!Customer & ": " & TEXT(!Amount, "$#,##0.00")`
Best contact	`=COALESCE(!Email, !Phone, "none on file")`
Tier	`=IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")`

A running total works because a computed column can read its own value on the row before. A formula can also read any computed column added before it, on the same row.

Where it differs from a spreadsheet

Row order comes from the query. [1] means the row the database sent before this one. For a running total or a change from last month, end the query with an ORDER BY so the rows arrive in the order you mean. Sorting the grid afterwards moves the values with their rows; it doesn't recompute them.
One formula per column. Like a calculated column in an Excel table, every row uses the same formula. There is no overriding a single cell.
Empty values are empty cells. A NULL from the database counts as 0 in arithmetic and as blank text, as an empty cell does in Excel. Above the first row is empty too.
Errors look like Excel's. Dividing by zero shows #DIV/0! in that cell, a misspelled function #NAME?, and so on. Wrap the formula in IFERROR as you would in Excel.
The type follows the formula. Date arithmetic gives a date column, comparisons give TRUE/FALSE, text functions give text. If an IF can return either a number or "", the column becomes text; return 0 instead to keep it numeric.
Very large results. A result too large to keep in memory is read from disk a page at a time, and formulas see only the rows of each page. A running total then starts over with each page.

Keep it with the query

The formula already lives in the query as a -- sqlly column: line. If you also click the lock in front of the result's name, SQLly writes the computed columns into the lock block together with the rest of the grid's setup: column order, widths, formats and colors.

Take it to Excel

Export the result to Excel, or use Open in Spreadsheet, and each computed column arrives as a real Excel formula. =!Qty * !Price on the seventh sheet row becomes =B7*C7, !Qty[1] becomes the row above, !Qty[$1] becomes B$2, and @rate becomes a named value in the workbook. Change a number in Excel and the column recalculates. The details are on Excel export.