EQL provides two filtering options: WHERE and HAVING. The WHERE clause is used to filter input records for an expression.
Its syntax is as follows:
WHERE <Boolean expression>
If an aggregation function is used with a WHERE clause, then the Boolean expression must be enclosed within parentheses. The aggregation functions are listed in the topic Aggregation functions.
In this example, the amounts are only calculated for sales in the West region. Then, within those results, only sales representatives who generated at least $10,000 are returned:
RETURN Reps AS SELECT SUM(Amount) AS SalesTotal WHERE Region = ‘West’ GROUP BY SalesRep HAVING SalesTotal > 10000
In the next example, a single statement contains two expressions. The first expression computes the total for all of the records and the second expression computes the total for one specific sales representative:
RETURN QuarterTotals AS SELECT SUM(Amount) As SalesTotal, SUM(Amount) WHERE (SalesRep = ‘Juan Smith’) AS JuanTotal GROUP BY QuarterThis would return both the total overall sales and the total sales for Juan Smith for each quarter. Note that the Boolean expression in the WHERE clause is in parentheses because it is used with an aggregation function (SUM in this case).