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.

The syntax is as follows:

AggregateFunction(Expression) WHERE (Filter)

For example:

RETURN NetSales AS SELECT
  SUM(Amount) WHERE (Type=‘Sale’)
    AS SalesTotal,
  SUM(Amount) WHERE (Type=‘Return’)
    AS ReturnTotal,
  SalesTotal – ReturnTotal AS Total
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.