Expressions
The Select query builder supports the use of expressions in the field list. Examples of expressions include "twice the age field", "a count of all name fields", and a substring of the title field. Be aware that many expressions may use SQL functions, and not all SQL functions are standardized across all databases. It is up to the module developer to ensure that only cross-database compatible expressions are used. (Refer to this list: Functions and operators)
To add an expression to a query, use the addExpression() method.
$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'timestamp', array(':offset' => 3600));
The first line above will add "COUNT(uid) AS uid_count" to the query. The second parameter is the alias for the field. In the rare case that alias is already in use, a new one will be generated and the return value of addExpression() will be the alias used. If no alias is specified, a default of "expression" (or expression_2, expression_3, etc.) will be generated.
The optional third parameter is an associative array of placeholder values to use as part of the expression.
Note that some SQL expressions may not function unless accompanied by a GROUP BY clause added with $query->groupBy(). It is up to the developer to ensure that the query that is generated is in fact valid.
Few more Expression Examples
Most of the following examples are from Backdrop core itself. I have modified some of them to make it simpler to understand.
// a column called changed will be added to result and its value will be 1
$query->addExpression('1', 'changed');
// example using CONCAT
$query->addExpression("CONCAT(:prefix, t.$id_field)", 'item_id', array(':prefix' => $type . '/'));
// placeholder , column name , assign value to placeholder
$query->addExpression(':index_id', 'index_id', array(':index_id' => 'some string or variable'));
// using MAX
$query->addExpression('MAX(i.invoice_id)', 'max_invoice_id');
// using SUM
$select->addExpression('SUM(c.chvotes)', 'votes');
// using CASE //https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case
$query->addExpression('
CASE ncs.last_comment_uid
WHEN 0 THEN ncs.last_comment_name
ELSE u2.name END', 'last_comment_name'
);
// CASE with like
$alias = $db_query->addExpression("CASE WHEN t.word LIKE :like_$alias THEN 1 ELSE 0 END", $alias, array(":like_$alias" => $like));
// Using MIN
$query->addExpression('min(l.lid)', 'lid');
// Using SUM
$first->addExpression('SUM(i.score * t.count)', 'calculated_score');
$this->addExpression('SUM(' . implode(' + ', $this->scores) . ')', 'calculated_score', $this->scoresArguments);
$query->addExpression("'" . LANGUAGE_NONE . "'", 'language');
$query->addExpression('0', 'delta');
$query->addExpression('SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))', 'torder');
$query->addExpression('0 + :changed', 'changed', array(':changed' => $changed));
// Using Case, Extensive example
$query->addExpression('
CASE
WHEN (MAX(i.invoice_id) < 5) THEN :less
WHEN (MAX(i.invoice_id) > 5 AND MAX(i.invoice_id) < 10) THEN :medium
ELSE :more END', 'is_it_sri_sri', array(
':less' => 'less',
':more' => 'more',
':medium' => 'medium')
); //https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case