...
| Code Block |
|---|
|
-- Select all invoices and their score value where the field 'status' either contains 'reminder',
-- 'discount' or 'delay', with the result list sorted in ascending order of alias s (score value).
SELECT *,SCORE() s FROM invoice WHERE status CONTAINS('reminder discount delay') ORDER BY s ASC; |
Aggregations
...
GROUP BY and COUNT(*)
...
Function
To determine the number of objects with special properties, queries can be aggregated. All objects with the same entry in the field specified after the GROUP BY clause are summarized. The number of objects is returned to the field 'OBJECT_COUNT' without the assignment of aliases.
...
| Code Block |
|---|
|
-- Select the number with alias 'c' and 'type' of all objects (system:object), aggregated by 'type',
-- with the result list sorted in descending order of alias c (number of objects).
SELECT COUNT(*) c,type FROM system:object GROUP BY type ORDER BY c DESC; |
SUM() Function
As of 2023 Autumn.
The SUM() function can be used in the SELECT statement of the search query. Similar to the SQL SUM function, it calculates the sum of numeric values for a specified numeric property. The result is retrieved as a single entry in the result list.
Simple Example
| Code Block |
|---|
|
-- Sum the values of all 'amount' properties
SELECT SUM(amount) FROM system:object; |
SUM() with Conditions
The SUM() function can be combined with a WHERE statement that specifies conditions.
| Code Block |
|---|
|
-- Sum the value of all 'amount' properties for objects created today
SELECT SUM(amount) FROM system:object WHERE system:creationDate IN today(); |
SUM() with Alias
It is possible to set an alias as which the SUM() result will be displayed.
| Code Block |
|---|
|
-- Sum the value of all 'amount' properties , return them with alias 'totalsum'
SELECT SUM(amount) AS totalsum FROM system:object; |
Multiple SUM() Requests
Within one search query, multiple SUM() requests can be processed.
| Code Block |
|---|
|
-- Sum the value of all 'amount' and all 'item' properties
SELECT SUM(amount) AS totalsum, SUM(items) totalitems FROM system:object; |
SUM() on Aggregations
As the SUM() function is a specific type of aggregation, it can be applied to other aggregations via GROUP BY as well.
| Code Block |
|---|
|
-- Sum the value of all 'amount' properties, grouped by 'supplier'
SELECT supplier, SUM(amount) AS totalsum FROM system:object GROUP BY supplier; |
Sorting by SUM() Results
To sort by a SUM() result, it is required to specify an alias.
| Code Block |
|---|
|
-- Sum the value of all 'amount' properties, order them descending
SELECT SUM(amount) AS totalsum FROM system:object ORDER BY totalsum DESC; |
SUM() for Constants
If a constant is passed to the SUM() function, it is used as scalar factor to be multiplied by the number of search results.
| Code Block |
|---|
|
-- Sum the value with constant 10, result should be 10x the count of hits
SELECT SUM(10) AS totalsum FROM system:object; |
...