Page tree
Skip to end of metadata
Go to start of metadata

Replacement of CLF (column-level formulas)

Since 2.10, formulas for cells use a simpler, more powerful language (see Formulas 2). Formulas for totals and columns (CLF, column-level formulas) keep using the old SQL syntax, which is very difficult to understand and inconsistent with the Formulas 2 syntax. We hope to remove the old SQL syntax and replace it in the future. The notable point is, formulas for columns currently don't update the contents of the column; They just replace what is displayed when viewing the table.

This page explains the old SQL syntax, used for CLFs, which is difficult to understand and may be replaced.

If you need to use formulas, please rather use cell-level formulas, using the Formulas 2 syntax.


With Totals, users can write a SQL formula and display results at the bottom of the tables. It requires a bit of SQL knowledge but it is just as powerful as Excel formulas. Start small!

In Column formulas only

Link to Google MapsThe field must be of type "Link"

= TO_URL('', address, address || ' on Google Maps')

Example: Link to 341 George Street, Sydney

Parameter 1: The url with %s where the expression will be replaced

Parameter 2: The column name with the searched expression

Parameter 3: Optional - The label of the link

Value from another table-

= othertable.column WHERE = x

Example: Display the Name of a person instead of their ID

othertable: The name of another table

column: The column you want to display

x: The column of the current table, which serves as a foreign key

Any other SQL-

(SELECT * FROM othertable)

Example: Display the value of another column

There is no '=' for SQL formulas.

Any SQL is permitted. It could be as simple as the name of another column, or a litteral label between single quotes ('Ok').

Parenthesis are optional for simple expressions.

In both Totals and Column formulas

Best score(grey lightbulb) BeginnerMAX(points)82
Average score(grey lightbulb) BeginnerROUND(AVG(points), 1)40.3
Formatting a currency(lightbulb) IntermediateTO_CHAR(SUM(price), 'L 999G999D99')$ 4,432.83
Formatting the result(lightbulb) IntermediateSUM(points) || ' points'824 points
Number of people without score(star) Expert(SELECT COUNT(*) FROM games WHERE points IS NULL)1
Advanced formatting(star) Expert(SELECT FORMAT('%s (%s points)', name, points)
FROM games ORDER BY points DESC LIMIT 1)
John (82 points)


Aggregation Operators

SQL has hundreds of operators (see Postgresql's documentation page). Here's the ones you may use most often.

Aggregation Operators on numbers




The min/max.
SUM(column)The sum of all number in the column
AVG(column)The average
ROUND(column, 2)Rounding to 2 decimals

Operators on text


TO_CHAR(SUM(price), 'L 999G999D99')

Also try the double-colon: SUM(price)::numeric::money

$ 432,111.02

  • L is replaced by the currency of the database,
  • 0 and 9 will be replaced by numbers (mandatory or optional)
  • G is the group separator (thousands)
  • D is the decimal separator
  • Decimals are rounded
column || ' American Dollars'Concatenation of 'column' and ' American Dollars'
CASE column WHEN 1 THEN 'One' ELSE 'Not one' ENDConditional result



Change to lowercase / uppercase
POSITION('president' IN column)Position of the substring 'president' in the text of the column
LENGTH(column)Length of the text


The formula you're writing is executed in a query, so you can write subqueries, provided you enclose it in parenthesis.

Sum if...(SELECT SUM(points) FROM games WHERE points > 24)Sum all points above 24
Join (or "Lookup" or "VLOOKUP")

(SELECT address
FROM people
WHERE = (SELECT name FROM games ORDER BY points DESC LIMIT 1))

Joins the two tables to get the address of the winner:

Table "games"
Table "people"
Marsha83/3 Darlinghurst Road
Your forumla will be inserted in a query of this form:
SELECT [formula 1], [formula 2], ...
FROM (the table)
  • No labels