CODEWITHSUNDEEP
sqlsql-servert-sql
Joel
Joel

Reputation: 105

How to filter rows by the result of a calculation

The below code creates a new column "ZScore" 

SELECT [Cardholder Name], [Debit Amount], ([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()) as [ZScore]
FROM ['Card Data']
ORDER BY [ZScore] DESC;

What I am trying to do is only display the rows where ZScore is >= 3. I have tried the following but everything seems to throw an error.

SELECT [Cardholder Name], [Debit Amount], ([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()) as [ZScore]
FROM ['PCard Output']
HAVING (([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ())) > 3
ORDER BY [ZScore] DESC;

What would be the correct way to only display rows where the calculated z score is >= 3?

Upvotes: 1

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Just use a subquery:

SELECT cd.*
FROM (SELECT [Cardholder Name], [Debit Amount], ([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()) as [ZScore]
      FROM ['Card Data']
     ) cd
WHERE ZScore > 3
ORDER BY [ZScore] DESC;

Upvotes: 5

Related Questions