Query including TOP and AVG

Question

This is the last problem I have to deal with in my application and I hope someone will help because I'm clueless, I did my research and cannot find a proper solution.

I have an 'University Administration' application. I need to make a report with few tables included.

The problem is in SQL Query i have to finish. Query needs to MAKE LIST OF BEST 'n' STUDENTS, and the condition for student to be 'best' is grade AVERAGE.

I have 3 columns (students.stID & examines.grades). I need to get an average of my 'examines.grades' column, sort the table from highest (average grade) to the lowest, and I need to filter 'n' best 'averages'.

The user would enter the filter number and as I said, the app needs to show 'n' best averages.

Problem is in my SQL knowledge (not mySQL literaly but T-SQL). This is what I've donne with my SQL query, but the problem lies in the "SELECT TOP" because when I press my button, the app takes average only from TOP 'n' rows selected.

SELECT TOP(@topParam) student.ID, AVG(examines.grades)
FROM            examines INNER JOIN
                         student ON examines.stID = student.stID
WHERE        (examines.grades > 1)

For example:

StudentID  Grade
1          2
2          5
1          5
2          2
2          4
2          2

EXIT:

StudentID  Grade_Average
1          3.5
2          3.25

Answer 1

Being impatient, I think this is what you are looking for. You didn't specify which SQL Server version you're using although.

DECLARE @topParam INT = 3;      -- Default

DECLARE @student TABLE (StudentID INT);     -- Just for testing purpose
DECLARE @examines TABLE (StudentID INT, Grades INT);

INSERT INTO @student (StudentID) VALUES (1), (2);

INSERT INTO @examines (StudentID, Grades)
VALUES (1, 2), (2, 5), (1, 5), (2, 2), (2, 4), (2, 2);

SELECT DISTINCT TOP(@topParam) s.StudentID, AVG(CAST(e.grades AS FLOAT)) OVER (PARTITION BY s.StudentID) AS AvgGrade
FROM @examines AS e
INNER JOIN @student AS s
    ON e.StudentID = s.StudentID
WHERE e.grades > 1
ORDER BY AvgGrade DESC;

If you'll provide some basic data, I'll adapt query for your needs.

Result:

StudentID   AvgGrade
--------------------
1           3.500000
2           3.250000

Quick explain:

Query finds grades average in derived table and later queries it sorting by it. Another tip: You could use WITH TIES option in TOP clause to get more students if there would be multiple students who could fit for 3rd position.

If you'd like to make procedure as I suggested in comments, use this snippet:

CREATE PROCEDURE dbo.GetTopStudents
(
    @topParam INT = 3
)
AS
BEGIN
    BEGIN TRY    
        SELECT DISTINCT TOP(@topParam) s.StudentID, AVG(CAST(e.grades AS FLOAT)) OVER (PARTITION BY s.StudentID) AS AvgGrade
        FROM examines AS e
        INNER JOIN student AS s
            ON e.StudentID = s.StudentID
        WHERE e.grades > 1
        ORDER BY AvgGrade DESC;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    END CATCH
END

And later call it like that. It's a good way to encapsulate your logic.

EXEC dbo.GetTopStudents @topParam = 3;

Answer 2

You should use the group by clause for counting average grades (in case examines.grades has an integer type, you should cast it to the floating-point type) for each student.ID and order by clause to limit your output to only top n with highest average grades:

select top(@topParam) student.ID
     , avg(cast(examines.grades as float)) as avg_grade
from examines
join student on examines.stID = student.stID
where (examines.grades > 1)
group by student.ID
order by avg_grade desc

Answer 3

There's no need for A Windowed Aggregate which returns the duplicate rows and then you need DISTINCT to remove them again. It's a simple aggregation and your original query was already quite close:

SELECT TOP(@topParam) student.ID, AVG(CAST(grade AS FLOAT)) as AvgGrade
FROM            examines INNER JOIN
                         student ON examines.stID = student.stID
WHERE        (examines.grades > 1)
group by student.ID
order by AvgGrade DESC