There is a table with test results:

enter image description here

You must display the average test score of schools in mathematics (subject code - 2) :

SELECT schoolid, avg(TestResult100) av FROM result WHERE SubjectCode = 2 GROUP BY SchoolID

enter image description here

Mathematics was handed over only by students from school 1001 and, of course, only the result of this school was displayed in the resulting table. But how to display the result of school 1002 ?

Yes, yes, I know - SQL Server displayed everything correctly, according to the request made. I would like to make such a request so that the result will be something like:

enter image description here

Is it possible to create a SQL query to display such information?

    2 answers 2

     SELECT schoolid, avg(case SubjectCode when 2 then TestResult100 end) av FROM result GROUP BY SchoolID

      LEFT JOIN to help: select all schools, and then combine them with the results.

       SELECT s.SchoolID, avg(r.TestResult100) av FROM result s LEFT JOIN result r ON s.SchoolID = r.SchoolID WHERE s.SubjectCode = 2 GROUP BY s.SchoolID