Perform Aggregate in Where clause

Question

I am wanting to perform the below query that essentially shows where a calculation is >= .70 or <= -.70 However, I get an error of

Msg 147, Level 15, State 1, Line 8
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Here is sample DDL - what is the appropriate way to write this query?

    Declare @Hamburger Table (empName varchar(200), e float, a float, saleid varchar(10))
Insert Into @Hamburger (empName, e,a, saleid) Values
('Jake', 362, 233, 'E111'), ('Jake', 1431, 2702, 'E112'), ('Blue', 849, 280, 'R222'), ('Blue', 1418, 299, 'R390')

Select empName, e, a, saleid, AmtGained = Sum(Coalesce(e,0))-Sum(Coalesce(a,0)),
DiscountPercent = Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0)
From @Hamburger
where ((Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) >= .70)
OR (Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) <= -.70))
Group By empName, saleid, e, a
Order By empName ASC

Answer 1

Just like the error says, you can't have aggregates in the WHERE clause. WHERE is logically before GROUP BY, so it filters rows before they are grouped, not after.

For filtering the groups, use HAVING (or push the GROUP BY into a subquery). eg

Declare @Hamburger Table (empName varchar(200), e float, a float, saleid varchar(10))

Insert Into @Hamburger (empName, e,a, saleid) Values
    ('Jake', 362, 233, 'E111'), ('Jake', 1431, 2702, 'E112'), ('Blue', 849, 280, 'R222'), ('Blue', 1418, 299, 'R390')

Select empName, e, a, saleid, AmtGained = Sum(Coalesce(e,0))-Sum(Coalesce(a,0)),
DiscountPercent = Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0)
From @Hamburger    
Group By empName, saleid, e, a
having ((Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) >= .70)
OR (Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) <= -.70))
Order By empName ASC