CODEWITHSUNDEEP
mysqlsqlaveragestandard-deviation
rinki nag
rinki nag

Reputation: 25

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

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

Upvotes: 0

Views: 369

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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

Upvotes: 1

Related Questions