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