SQL Query to find whether a entry if greater than sum of average of whole column +standard deviation of whole column

Question

Let's say I have a table with

student	Marks
john	30
ron	40

I have to write a query which gives me output as student name and avg(marks column)+standardeviation(marks column) or 0 , if student marks is greater than avg(marks column)+standardeviation(marks column) then avg(marks column)+standardeviation(marks column) else 0

I have written a query but it does not work

select student, case when marks>(select avg(marks)+stddev_pop(marks) from students) then  avg(marks column)+standardeviation(marks column) when  marks=(select avg(marks)+stddev_pop(marks) then marks else 0 end as final_marks from students 

Please can anyone suggest any way

Answer 1

Close. Use window functions. And in MySQL, you don't need a case because booleans are supported:

select s.student,
       (s.marks > avg(s.marks) over () + stddev_pop(s.marks) over ()) as final_marks
from students s;

In older versions of MySQL, I would recommend a subquery in the from clause:

select s.*,
       (s.marks > ss.avg_marks + ss.stddev_pop_marks) as final_marks
from students s cross join
     (select avg(s.marks) as avg_marks,
             stddev_pop(s.marks) as stddev_pop_marks
      from students
     ) ss