CODEWITHSUNDEEP
sqlcountaverage
Marjan Bilafer
Marjan Bilafer

Reputation: 31

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

Upvotes: 1

Views: 119

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521599

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;

Upvotes: 4

Karlheim
Karlheim

Reputation: 121

Somehting like this should work

    WITH _CTE AS 
(
    SELECT StudentId, AVG(Grade) as avgGrade
    FROM MyTalbe
    GROUP BY StudentId
)
SELECT COUNT(*)
FROM _CTE
WHERE avgGrade > 7.6
AND avgGrade < 8.3

Upvotes: 0

Hovhannes Manushyan
Hovhannes Manushyan

Reputation: 50

You should use having clause here instead of where. Also sum(count(distinct id)) could be used to count the number of values. Alternatively you could use a subquery to achieve the same result.

select sum(count(distinct id)) from table_name
group by id
having AVG(grade) BETWEEN  7.6 and 8.3;

Upvotes: 0

Related Questions