doe, Sql Server - rextester

Language: Layout:

CREATE TABLE Student_Marks(Marks_ID INT, Mark_Name NVARCHAR(5))
CREATE TABLE Student(Student_ID INT, Student_Name NVARCHAR(100), Marks_ID INT)

INSERT INTO Student_Marks VALUES(1, 'A')
INSERT INTO Student_Marks VALUES(2, 'B')
INSERT INTO Student_Marks VALUES(3, 'C')
INSERT INTO Student_Marks VALUES(4, 'D')
INSERT INTO Student_Marks VALUES(5, 'E')

INSERT INTO Student VALUES(1, 'John Doe', 1)
INSERT INTO Student VALUES(1, 'John Doe', 1)
INSERT INTO Student VALUES(1, 'John Doe', 2)
INSERT INTO Student VALUES(1, 'John Doe', 3)
INSERT INTO Student VALUES(1, 'John Doe', 4)
INSERT INTO Student VALUES(1, 'John Doe', 5)
INSERT INTO Student VALUES(1, 'Jane Doe', 1)
INSERT INTO Student VALUES(1, 'Jane Doe', 2)
INSERT INTO Student VALUES(1, 'Jane Doe', 2)
INSERT INTO Student VALUES(1, 'Jane Doe', 2)
INSERT INTO Student VALUES(1, 'Jane Doe', 4)

SELECT		TOP 1 students.Student_Name, 
			COUNT(marks.Mark_Name) as TotalOfGradeA
FROM		Student students
INNER JOIN	Student_Marks marks ON students.Marks_ID = marks.Marks_ID
WHERE		marks.Mark_Name = 'A'
GROUP BY	students.Student_Name
ORDER BY	COUNT(marks.Mark_Name) DESC

View schema
Execution time: 0,03 sec, rows selected: 1, rows affected: 16, absolute service time: 0,17 sec