Aggregate functions

An aggregate describes a set of rows. Before writing the expression, decide whether one row represents an order, an item, a customer, or an event. The examples use lake.uploads.orders, a sample connector.dataset.table with one row per order.

Counts and distinct values

COUNT(*) measures rows. COUNT(column) excludes rows where that column is null. COUNT(DISTINCT column) measures different non-null values.

SELECT COUNT(*) AS order_rows,
       COUNT(customer_id) AS rows_with_customer,
       COUNT(DISTINCT customer_id) AS customers
FROM lake.uploads.orders;

A missing customer ID does not contribute to either customer count. Distinct customer counts cannot generally be added across countries or periods because a customer may occur in more than one group.

Totals and averages

SUM(value) adds non-null inputs. AVG(value) averages non-null inputs. Do not replace missing values with zero unless zero is the intended business meaning.

SELECT country,
       SUM(revenue) AS revenue,
       AVG(revenue) AS average_order_revenue,
       COUNT(revenue) AS orders_with_revenue
FROM lake.uploads.orders
GROUP BY country
ORDER BY revenue DESC
LIMIT 100;

An average of group averages is not a whole-population average unless all groups have equal weight. Combine sums and counts, then divide. On an empty input, count is zero while sum and average normally return null.

Minimum and maximum

MIN and MAX choose an extremum within each group. A maximum amount does not identify the rest of the row that contained it; use a ranked query when you need the corresponding order.

SELECT customer_id,
       MIN(order_date) AS first_order_date,
       MAX(order_date) AS latest_order_date,
       MAX(revenue) AS largest_order_revenue
FROM lake.uploads.orders
GROUP BY customer_id
LIMIT 100;

Conditional metrics

FastBI’s COUNT_IF(condition), SUM_IF(value, condition), and AVG_IF(value, condition) make conditions explicit. They are workbench functions; FastBI handles their execution.

SELECT country,
       COUNT_IF(status = 'completed') AS completed_orders,
       SUM_IF(revenue, status = 'completed') AS completed_revenue,
       AVG_IF(revenue, status = 'completed') AS average_completed_revenue
FROM lake.uploads.orders
GROUP BY country
LIMIT 100;

Review the function semantics for empty matches, null inputs, and argument order. For custom rules, put a CASE expression inside an aggregate.

Boolean summaries

BOOL_AND and BOOL_OR summarize Boolean conditions. Null inputs and an empty group require care: absence of a false value is not evidence that every source row was observed. Verify these signatures on external connectors.

SELECT customer_id,
       BOOL_OR(status = 'refunded') AS has_refunded_order,
       BOOL_AND(status = 'completed') AS all_known_statuses_completed
FROM lake.uploads.orders
GROUP BY customer_id
LIMIT 100;

Collected values

STRING_AGG(text, separator) produces a text summary. ARRAY_AGG(value) collects values into an array. These functions are recognized by the workbench, but source-specific ordering and modifiers need separate support. Do not rely on collection order without a supported explicit ordering.

WITH countries AS (
  SELECT DISTINCT customer_id, country
  FROM lake.uploads.orders
  WHERE country IS NOT NULL
)
SELECT customer_id, STRING_AGG(country, ', ') AS observed_countries
FROM countries
GROUP BY customer_id
LIMIT 100;

Large collections can create very large result cells. Prefer counts or a separate detail query when the list has no useful upper bound. Array results may cross a transfer boundary as text; see complex values.

Ratios and weighted measures

Calculate a ratio from the final numerator and denominator rather than averaging row-level percentages.

SELECT country,
       PERCENT(COUNT_IF(status = 'completed'), COUNT(*)) AS completion_percent
FROM lake.uploads.orders
GROUP BY country
LIMIT 100;

Group filters and join fanout

Use HAVING COUNT(*) >= 10 to require at least ten rows per group. A one-to-many join can multiply amounts before aggregation; aggregate to the intended join grain first. Check cross-source examples for that pattern.

Approximate aggregates and statistical extensions are not in the current workbench function supported list. Do not substitute an unsupported function for an exact measure without verifying the execution path.