How can I make a selection from a MS SQL database?

Question

SQL

Hello!
There is a schema for the database of debtors by subject at the university.

I'm interested in the following sample:

For each group, select the total number of debtors and the number of debtors who have more than two debts

Main tables involved in the query:

• Students (StudentId(PK), GroupNumber, RecordBookNumber(pass number), FirstName, LastName, MiddleName)
  
• Subjects (SubjectId(PK), Name)
  
• StudentsSubjects (StudentsSubjectsId(PK), SubjectId(FK), TeacherId(FK), MarkId(FK), ControlTypeId(FK), ControlPeriod(FK), CuratorID(FK))
  

I can extract some data separately.

Number of debts in the group

select Students.GroupNumber as groupNum, Count(StudentsSubjects.StId) as Kol_dolgov
from StudentsSubjects, Students
where Students.RecordBookNumber = StudentsSubjects.StId
group by Students.GroupNumber
order by groupNum

Number of debtors with more than two debts

select t1.Grp, count(t1.Rbn) as Kol_dol_bol2
from
(select Students.GroupNumber as Grp, Students.RecordBookNumber as Rbn, count(StudentsSubjects.SubjId) as Kol_dolg
from Students,StudentsSubjects, Subjects</li>
where Students.RecordBookNumber = StudentsSubjects.StId and Subjects.SubjectId = StudentsSubjects.SubjId
group by Students.RecordBookNumber, Students.GroupNumber
having count(StudentsSubjects.SubjId) > 2) as t1
group by t1.Grp

Number of debtors in the group

select Students.GroupNumber as Grp, count(Students.RecordBookNumber) as Kol_dolgnikov
from Students
group by Students.GroupNumber
order by 1

But I can not combine all this into one table in any way. Could you suggest how to do this and whether it is possible at all, given the given database schema.

Answer 1

Too lazy to write from the phone. Not the best solution, but put two queries in a subrequest and join on the common field.
select *
from (query 1) as query1
left outer join (query 2) as query2 on query1.group = query2.group. There is a more elegant option.

Answer 2

But I can not combine all this into one table in any way.

for such cases, the JOIN operator was invented in various interpretations.
wiki