Using SELECT AS expressions to calculate derived attributes

LQL statements typically use expressions to compute one or more derived attributes.

Each aggregation operation can declare an arbitrary set of named expressions, sometimes referred to as derived attributes, using SELECT AS syntax. These expressions represent aggregate analytic functions that are computed for each aggregated record in the statement result.

Important: Derived attribute names must be NCName-compliant. They cannot contain spaces or special characters. For example, the following statement would not be valid:

RETURN foo AS SELECT AVG(Price) AS "Average Price"

The space would have to be removed:

RETURN foo AS SELECT AVG(Price) AS "AveragePrice"

Supported aggregation functions

LQL supports the following aggregation functions:

Function	Description
AVG	Computes the arithmetic mean value for a field.
COUNT	Counts the number of records with valid non-null values in a field for each GROUP BY result.
COUNTDISTINCT	Counts the number of unique, valid non-null values in a field for each GROUP BY result.
MAX	Finds the maximum value for a field.
MIN	Finds the minimum value for a field.
MEDIAN	Finds the median value for a field.
STDDEV	Computes the standard deviation for a field.
ARB	Selects an arbitrary but consistent value from the set of values in a field.
SUM	Computes the sum of field values.
VARIANCE	Computes the variance (that is, the square of the standard deviation) for a field.

Supported numeric functions

LQL supports the following numeric functions:

Function	Description
addition	The addition operator `(+)`.
subtraction	The subtraction operator `(-)`.
multiplication	The multiplication operator `(*)`.
division	The division operator `(/)`.
ABS	Returns the absolute value of `n`. If `n` is `0` or a positive integer, returns `n`. Otherwise, `n` is multiplied by -1.
CEIL	Returns the smallest integer value not less than `n`.
EXP	Exponentiation, where the base is `e`. Returns the value of `e` (the base of natural logarithms) raised to the power of `n`.
FLOOR	Returns the largest integer value not greater than `n`.
LN	Natural logarithm. Computes the logarithm of its single argument, the base of which is e.
LOG	Logarithm. `log(n, m)` takes two arguments, where n is the base, and m is the value you are taking the logarithm of.
MOD	Modulo. Returns the remainder of `n` divided by `m`. LQL uses the `fmod` floating point remainder, as defined in the C/POSIX standard.
ROUND	Returns a number rounded to the specified decimal place. The unary version drops the decimal (non-integral) portion of the input. The binary version allows you to set the number of spaces at which the number is rounded: Positive second arguments specified to this function correspond to the number of places that must be returned after the decimal point. For example, `ROUND(123.4567, 3) = 123.457` Negative second arguments correspond to the number of places that must be returned before the decimal point. For example, `ROUND(123.4567, -3) = 100.0`
SIGN	Returns the sign of the argument as -1, 0, or 1, depending on whether n is negative, zero, or positive.
SQRT	Returns the nonnegative square root of `n`.
TRUNC	Returns the number `n`, truncated to `m` decimals. If `m` is 0, the result has no decimal point or fractional part. The unary version drops the decimal (non-integral) portion of the input, while the binary version allows you to set the number of spaces at which the number is truncated.
SIN	The sine of `n`, where the angle of `n` is in radians.
COS	The cosine of `n`, where the angle of `n` is in radians.
TAN	The tangent of `n`, where the angle of `n` is in radians.
POWER	Returns the value of `n` raised to the power of `m`.
TO_DURATION	Casts an integer into a number of milliseconds so that it can be used as a duration. When TO_DURATION is given a value of type double, it removes the decimal portion and converts the integer portion to a duration.

COALESCE expression

The COALESCE expression allows for user-specified null-handling.

You can use the COALESCE expression to evaluate records for multiple values and return the first non-null value encountered, in the order specified. The following requirements apply:

You can specify two or more arguments to COALESCE
Arguments that you specify to COALESCE should all be of the same type
You cannot specify managed attributes as arguments to COALESCE. However, if you a managed attribute is mapped to a standard attribute, you can specify this standard attribute to COALESCE to create a valid query.
The COALESCE expression can only be used in a SELECT clause, and not in other clauses (such as WHERE)

In the following example, all records without a specified price are treated as zero in the computation:

AVG(COALESCE(price, 0))

COALESCE can also be used without aggregation, for example:

SELECT COALESCE(price, 0) AS price_or_zero WHERE