CODEWITHSUNDEEP
sqlcaseaverage
Giusepe Moreno
Giusepe Moreno

Reputation: 69

SQL Select Case and Average

Hello I have a table with the tenure (integer, calculated in weeks) of employees in which the tenure is automatically calculated. I'm then trying to do an average tenure per week. However I want to cap the max tenure an employee can have as 20 (in weeks).

What I've go so far is this, but it's not working:

SELECT week,
AVG(CASE Tenure WHEN Tenure > '20' THEN '20' ELSE Tenure END) as Tenure
FROM table
GROUP BY week
ORDER BY week

Am I on the right track? Please help me here. I've tried looking at other similar questions but no success.

Thanks in advance,

Giusepe Moreno

Upvotes: 0

Views: 6416

Answers (1)

hkutluay
hkutluay

Reputation: 6944

Try this

SELECT week,
AVG(CASE WHEN Tenure > 20 THEN 20 ELSE Tenure END) 
FROM table
GROUP BY week
ORDER BY week

Upvotes: 6

Related Questions