Replacement of CLF (columnlevel formulas)
Since 2.10, formulas for cells use a simpler, more powerful language (see Formulas 2). Formulas for totals and columns (CLF, columnlevel 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 celllevel 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
Example  Prerequisite  Formula  Parameters 

Link to Google Maps  The field must be of type "Link"  = TO_URL('http://www.google.com?q=%s', 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
Example  Level  Formula  Result 

Best score  Beginner  MAX(points)  82 
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) 
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
Operator  Result 

MIN(column) MAX(column)  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
Operator  Result 

TO_CHAR(SUM(price), 'L 999G999D99') Also try the doublecolon: SUM(price)::numeric::money  $ 432,111.02

column  ' American Dollars'  Concatenation of 'column' and ' American Dollars' 
CASE column WHEN 1 THEN 'One' ELSE 'Not one' END  Conditional result 
LOWER(column) UPPER(column)  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 
Subqueries
The formula you're writing is executed in a query, so you can write subqueries, provided you enclose it in parenthesis.
Example  Query  Result  

Sum if...  (SELECT SUM(points) FROM games WHERE points > 24)  Sum all points above 24  
Join (or "Lookup" or "VLOOKUP")  (SELECT address  Joins the two tables to get the address of the winner:

SELECT [formula 1], [formula 2], ... FROM (the table) LIMIT 1