Average using case-CodePudding

I am trying to get a column that returns 'yes' or 'no' if the average price is higher or lower than 6000. the script I am writing returns an error saying "Operand data type varchar is invalid for avg operator"

here is my script, how should I correct it?

select avg(case when BASIC_SALARY >6000 then 'yes'
else 'no' 
end )as 'above_6000'
from workers_table

CodePudding user response：

You just need to change the position of the average function.

SELECT CASE WHEN AVG(BASIC_SALARY) >6000 
            THEN 'yes'
            ELSE 'no' 
       END AS 'above_6000'
FROM workers_table