There is a table of exam results in Russian language and mathematics:

declare @Results Table ( SchoolID nvarchar(200), ParticipID nvarchar(200), SubjectCode int, TestResult5 int ) --subjectcode: 1-русский язык, 2-математика insert into @Results VALUES ('0001', 'Шахабов_Адам', 1, 4), ('0001', 'Асвадов_Аюб', 1, 3), ('0001', 'Майрукаев_Бекхан', 1, 3), ('0001', 'Шахабов_Адам', 2, 3), ('0001', 'Асвадов_Аюб', 2, 4), ('0001', 'Майрукаев_Бекхан', 2, 5), ('0002', 'Цуригова_Зайнап', 2, 5), ('0002', 'Майрукаев_Роза', 2, 4), ('0002', 'Муциев_Адлан', 2, 3), ('0002', 'Цуригова_Зайнап', 1, 3), ('0002', 'Майрукаев_Роза', 1, 4), ('0002', 'Муциев_Адлан', 1, 5)

enter image description here

Precondition: It is necessary to extract the list of schools ( SchoolID ), in which the number of participants who received a mark of 3 in the Russian language is more than 1.

How I do it:

 select schoolid from (select schoolid, SUM(CASE WHEN subjectcode = 1 and testresult5 = 3 then 1 else 0 end) c from @Results group by schoolid) temp_table where c > 1

enter image description here

Necessary: ​​to optimize this query.

Of course, in this example, the code does not look quite large. I specifically showed a small example. Am I doing everything right? Is there any way to do without a subquery in this case?

    2 answers 2

    HAVING will help you:

     SELECT schoolid, COUNT(ParticipID) FROM @Results WHERE subjectcode = 1 AND testresult5 = 3 GROUP BY schoolid HAVING COUNT(ParticipID) > 1

    http://sqlfiddle.com/#!9/fe6b2/1/0

    https://ru.wikipedia.org/wiki/Having_(SQL)

      In addition to the answer of Sergey Vodakov

      If you need to pull out only schoolid from the request, you can write without count(participid) :

       select r.schoolid from results r where r.subjectcode = 1 and r.testresult5 = 3 group by r.schoolid having count(distinct r.participid) >= 2

      sqlfiddle