Calculate number of people over average-CodePudding

I am trying to calculate how many people have a grade higher than average.
What I have currently instead returns the number of students and when I delete "=" from ">=" it returns 0.

SELECT count(*) 
FROM data.students
WHERE grade IN (SELECT grade 
               FROM data.students
               GROUP BY grade HAVING grade >= AVG(grade));

If I put an integer instead of avg() function I get good results. What am I doing wrong?

CodePudding user response：

Try this :

SELECT count(*)
FROM (
       SELECT grade >= AVG(grade) OVER () AS tst
       FROM data.students
     ) AS a
WHERE a.tst= True

CodePudding user response：

Computing the avg in a subquery is probably fastest:

SELECT count(*)
FROM   data.students
WHERE  grade > (SELECT avg(grade) FROM data.students);

> instead of >=, since you said "a grade higher than average".

What am I doing wrong?

In your subquery, GROUP BY grade aggregates to one row per distinct value of grade. avg(grade) is bound to be exactly the same as grade for every row (except grade IS NULL). Explains what you saw.
But the query way needlessly complex to begin with.