Per-aggregation filters

Each aggregation can have its own filtering WHERE clause. Aggregation function filters filter the inputs to an aggregation expression. They are useful for working with sparse or heterogeneous data. Only records that satisfy the filter contribute to the calculation of the aggregation function.

Per-aggregate WHERE filters are indeed applied pre-aggregation. The reason is that if it is delayed until post-aggregation, the implementation may not necessarily have access to all of the columns that it needs.

The per-aggregation syntax is:

AggregateFunction(Expression) WHERE (Filter)

For example:

RETURN NetSales AS 
SELECT
  SUM(Amount) WHERE (Type='Sale') AS SalesTotal,
  SUM(Amount) WHERE (Type='Return') AS ReturnTotal,
  ARB(SalesTotal – ReturnTotal) AS Total
FROM SaleState
GROUP BY Year, Month, Category

This is the same as:

SUM(CASE WHEN Type='Sale' THEN Amount END) AS SalesTotal,
SUM(CASE WHEN type='Return' THEN Amount END) AS ReturnTotal
...

Note:

These WHERE clauses also operate on records, not assignments, just like the statement-level WHERE clause. A source record will contribute to an aggregation if it passes the statement-level WHERE clause and the aggregation's WHERE clause.