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.
AggregateFunction(Expression) WHERE (Filter)
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
SUM(CASE WHEN Type='Sale' THEN Amount END) AS SalesTotal, SUM(CASE WHEN type='Return' THEN Amount END) AS ReturnTotal ...