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 Maps||The 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 othertable.id = 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
|Average score||Beginner||ROUND(AVG(points), 1)||40.3|
|Formatting a currency||Intermediate||TO_CHAR(SUM(price), 'L 999G999D99')||$ 4,432.83|
|Formatting the result||Intermediate||SUM(points) || ' points'||824 points|
|Number of people without score||Expert||(SELECT COUNT(*) FROM games WHERE points IS NULL)||1|
|Advanced formatting||Expert||(SELECT FORMAT('%s (%s points)', name, points)|
FROM games ORDER BY points DESC LIMIT 1)
|John (82 points)|
SQL has hundreds of operators (see Postgresql's documentation page). Here's the ones you may use most often.
Aggregation Operators on numbers
|SUM(column)||The sum of all number in the column|
|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
|column || ' American Dollars'||Concatenation of 'column' and ' American Dollars'|
|CASE column WHEN 1 THEN 'One' ELSE 'Not one' END||Conditional 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")|
Joins the two tables to get the address of the winner: