0

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
1
  • 2
    Try moving the condition to a HAVING clause...
    – joanolo
    Commented Jul 10, 2017 at 0:29

1 Answer 1

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.