I have a struggle in adding this two queries in one query-CodePudding

Select s.SID , s.Lastname, s.Firstname, s.MI, g.YearLevel, g.Course,
                count(s.SID) As 'Number Of Passed Subject'
                From student s 
                inner Join grades g on g.StudentID = s.SID
                Where g.Completion = 'Passed' and g.YearLevel = '4th Year'
                group by g.YearLevel, g.Course
                order by g.YearLevel, g.Course;
            
Select s1.SID , s1.Lastname, s1.Firstname, s1.MI, g1.YearLevel, g1.Course,
               SUM(su.LecUnit) As 'Total of Units Passed' 
               From grades g1
               INNER Join student s1 ON s1.SID = g1.StudentID 
               INNER Join subjects su ON su.SubjectCode = g1.SubjectCode 
               WHERE g1.Completion = 'Passed' AND g1.YearLevel = '4th Year'
               GROUP BY g1.Course, g1.YearLevel
               order by g1.Course, g1.YearLevel;

CodePudding user response：

Use the second query as the starting point, and then add a distinct count of student courses for the number of passed subjects. The logic here is that the additional join to the subjects table might duplicate each intermediate record from the first two joins. Taking the distinct count removes this possible duplication.

Select s1.SID, s1.Lastname, s1.Firstname, s1.MI,
       COUNT(DISTINCT g1.Course) As `Number Of Passed Subject`,
       SUM(su.LecUnit) As `Total of Units Passed`
FROM student s1
INNER JOIN grades g1 ON s1.SID = g1.StudentID
INNER JOIN subjects su ON su.SubjectCode = g1.SubjectCode
WHERE g1.Completion = 'Passed' AND g1.YearLevel = '4th Year'
GROUP BY 1
ORDER BY 1;

Note that I am also grouping only by s1.SID. Assuming SID be the primary key from that table, it covers all columns from the student table. The other table fields you were selecting probably don't belong there.