Index...
Centrallix Documentation
|
6.5 GROUP BY Clause
Grouping and Aggregates
Centrallix supports grouping / aggregating data using a GROUP BY clause and aggregate functions. If aggregate functions are used without a GROUP BY clause, the entire query is considered a single group, resulting in a one-row result set.
Centrallix differs a little bit from the standard SQL semantics in its handling of the GROUP BY clause. It is not necessary to list every non-aggregate field in the GROUP BY clause, only enough to specify the grouping that is desired. This permits, for example, doing a GROUP BY in a query containing a SELECT *. This is similar to MySQL semantics, and different from those of, for instance, Sybase and MS SQL Server.
Examples:
SELECT count(1), sum(:d:size) from /mydirectory d
SELECT sum(:d:name + ', ') from /mydirectory d
SELECT :d:owner, sum(:d:size) from /mydirectory d GROUP BY :d:owner
Aggregate Functions
Centrallix currently supports the aggregate functions sum(), count(), avg(), min(), and max(). Also supported are first() and last().
A column name or a constant value must be given to count(). Using "count(1)" is semantically the same as "count(*)" would be. When using a field name for count(), the row is not counted if the field is NULL.
The sum() function also has an interesting behavior: Centrallix's plus "+" operator allows string concatenation, so sum()ing a string field will return the concatenation of those strings.
With the exception of count(), these functions return NULL if no records were matched. count() in that case returns 0 (zero).
Comments...
(none yet)
Add a Comment...
|
