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