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 ...