Search code examples
sqlcountaverage

How to count records with averages in range

I need to count number of students with average grade from 7.6 to 8.3 I tried

Select count(*) from tabele
Where AVG(grade)> 7.6
and AVG(Grade)<8.3 
Group by id

But there is always an error

Solution

  • The approach below is to first aggregate by student id and assert the average grade requirements in a HAVING clause. Then, subquery to find the count of such matching students.

    SELECT COUNT(*)
FROM
(
    SELECT id
    FROM yourTable
    GROUP BY id
    HAVING AVG(Grade) > 7.6 AND AVG(Grade) < 8.3
) t;

    Assuming your RDBMS support window functions, here is one way we can get the count with a single query:

    SELECT DISTINCT COUNT(*) OVER () AS total_cnt
FROM yourTable
GROUP BY id
HAVING AVG(Grade) > 7.6 AND AVG(Grade) < 8.3;