This article introduces aggregate functions and their uses in Knowi. Aggregate functions allow you to summarize or change the granularity of your data.
| Function | Syntax | Purpose |
| count | count(fieldName) | Returns the count of non-null values in the column |
| sum | sum(fieldName) | Returns the sum of the column |
| avg | avg(fieldName) | Returns the average of the column |
| distinct | distinct(fieldName) | Returns the count of distinct non-null values in the column |
| max | max(fieldName) | Returns the maximum of an expression across all records. |
| min | min(filedName) | Returns the minimum of an expression across all records. |
| sd | sd(fieldName) | Returns the statistical standard deviation of all values in the given expression |
| median | median(fieldName) | Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored. |
Aggregations functions enable grouping/dimensions from the data.
Without GROUP BY
select sum(sent)
select sum(sent), avg(sent), count(*), median(sent), max(sent), min(sent)
With GROUP BY
Enables aggregations based on one or more groups/dimensions.
select sum(sent) as Total Sent, Customer group by Customer
ARRAY
Combines values on multiple rows of a given field into an array based on group by field(s).
ARRAY(<field>)
ARRAY(<field>, <remove-duplicates-flag>)
ARRAY(<field>, <remove-duplicates-flag>, <filter-out-null-flag>)
select Stock, array(Price) as Trends group by Stock
select Stock, array(Price, true, true) as Trends group by Stock