How to display the number of records whose values ​​are above the average?

Question

Microsoft Access

Task: the number of students whose score is above the average for the faculty

SELECT Count(*) AS [count], Round(Avg(People.[Средний балл последней сессии]), 1 ) AS AvgP
FROM People
WHERE (((People.[Средний балл последней сессии]) > AvgP));

Such a code in an SQL query inside Access asks to enter AvgP, how to make the query work with AvgP as an average

Answer 1

You cannot use an alias. In WHERE use full function. And are you sure you need WHERE, not HAVING?