CODEWITHSUNDEEP
sqloracle-database
cise
cise

Reputation: 85

How to use CASE after arithmetic in Oracle SQL

I was wondering how I would use CASE after calculating. Basically I want to calculate a value and if that value returns larger than 1 I want to set it to 1.

Heres what I got so far:

SELECT (HSDIPLOMA + BACHELORDEGREE) / (SELECT AVG(HSDIPLOMA + BACHELORDEGREE) FROM EDUCATIONPROFILE) as res, case when res > 1 then 1 else res END AS result FROM EDUCATIONPROFILE ORDER BY cid ASC

However, I get an invalid identifer error "RES": invalid identifier. Where is my mistake? Thanks in advance.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use window functions and LEAST():

SELECT LEAST( (HSDIPLOMA + BACHELORDEGREE) / AVG(HSDIPLOMA + BACHELORDEGREE) OVER (),
              1
            ) as res
FROM EDUCATIONPROFILE
ORDER BY cid ASC

Upvotes: 1

Related Questions