Aggregations – Documentation and Support | Knowi

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 distinct non-`null` values in the column
max	max(fieldName)	Returns the maximum of an expression across all records
min	min(fieldName)	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

HAVING - Filtering Aggregated Results

The HAVING clause filters results after GROUP BY aggregation, unlike WHERE which filters before aggregation.

  select Customer, sum(sent) as Total_Sent 
  group by Customer 
  having sum(sent) > 100000

You can also use column aliases in HAVING:

  select Customer, sum(sent) as Total_Sent 
  group by Customer 
  having Total_Sent > 100000

Multiple conditions can be combined:

  select Customer, sum(sent) as Total, avg(opened) as Avg_Opened
  group by Customer
  having Total > 50000 and Avg_Opened > 1000

In Analyze Mode UI: The HAVING functionality is available through the "Aggregation Filters" section in the Data Transformation tab. Simply drag aggregated metrics into this section to filter on aggregated values.

Window Functions (OVER with PARTITION BY)

Window functions perform calculations across a set of rows that are related to the current row, similar to aggregate functions but without collapsing the rows into a single output row. Currently, only aggregate window functions are supported.

Syntax

AGGREGATE_FUNCTION(column) OVER (PARTITION BY partition_column[, ...])
AGGREGATE_FUNCTION(column) OVER ()

Supported Window Functions

SUM(column) OVER (PARTITION BY ...) - Sum values within each partition
AVG(column) OVER (PARTITION BY ...) - Average values within each partition
COUNT(*) OVER (PARTITION BY ...) - Count rows within each partition
MAX(column) OVER (PARTITION BY ...) - Maximum value within each partition
MIN(column) OVER (PARTITION BY ...) - Minimum value within each partition

All window functions also support OVER () without PARTITION BY to calculate over all rows.

Examples:
Get total sales per category for each row:

select id, category, amount, SUM(amount) OVER (PARTITION BY category) as category_total

Get maximum salary per department:

select employee_id, department, salary, MAX(salary) OVER (PARTITION BY department) as max_dept_salary

Count orders per customer:

select order_id, customer, order_date, COUNT(*) OVER (PARTITION BY customer) as customer_order_count

Get grand total for all rows:

select id, amount, SUM(amount) OVER () as grand_total

Mix window functions with and without partitions:

select id, region, sales, 
SUM(sales) OVER () as total_sales,
SUM(sales) OVER (PARTITION BY region) as regional_sales

Multiple partitions:

select year, quarter, region, revenue, SUM(revenue) OVER (PARTITION BY year, quarter) as quarter_total

Multiple window functions in one query:

select product, region, sales,
SUM(sales) OVER (PARTITION BY region) as region_total,
AVG(sales) OVER (PARTITION BY product) as product_avg

Notes:

Window functions with ORDER BY inside OVER() clause are not currently supported
RANK() window function is not currenlty supported
Refer to Window Functions for more information.

Without GROUP BY

With GROUP BY

HAVING - Filtering Aggregated Results

Window Functions (OVER with PARTITION BY)

Related articles