How to join 3 tables and get the field with 2 different conditions?

I have 3 tables: Students, Courses (important field Points), StudentCourses (important field Grade).

The exam is passed if Grade is greater than or equal to 70, so student will receive Points. If Grade is less than 70, student will not receive Points. Student is graduated if sum of his credits is greater than 10.

I need to select StudentName, sum of his PASSED Points, average of his ALL grades. I could select StudentName, sum of his PASSED Points and average of his PASSED grades. But I need average of ALL grades.

I wrote the following query.

SELECT s.Name, SUM(c.CreditPoints) AS CreditPoints, AVG(sc.Grade) AS 
AverageGrade FROM Students s JOIN StudentCourse sc
ON s.Id = sc.StudentId
JOIN Courses c ON c.Id = sc.CourseIdenter code here
WHERE sc.Grade >= 70
GROUP BY s.Name
HAVING SUM(CreditPoints) > 10
ORDER BY CreditPoints

Answer

Although you want to select only the student’s name make sure that you group by student’s id also (I believe there is a column in the Students table) becaus ethere would be 2 students with the same name.
You must not use this condition:

WHERE sc.Grade >= 70

because this will remove the rows with Grade < 70 which you need to calculate the average.
So the sum of creditpoints will be calculated with a CASE statement:

select 
  s.Id, s.Name,
  sum(case when sc.grade >= 70 then c.creditpoints else 0 end) CreditPoints,
  avg(sc.grade) AverageGrade 
from students s 
inner join studentcourse sc on s.id = sc.studentid 
inner join courses c on c.id = sc.courseid
group by s.id, s.Name
having sum(case when sc.grade >= 70 then c.creditpoints else 0 end) > 10
order by CreditPoints desc;

Since you are using SQL Server you can’t use the alias CreditPoints in the HAVING clause so you have to repeat the CASE statement.