SQL group by 와 기본적인 문법 이해

코딩인터뷰 완전분석에 나온 글을 인용했습니다.

 

조건

table

Courses : courseID ( primaryKey ) , courseName , TeacherID

Teachers : TeacherID ( primaryKey ) , TeacherName

Student : StudenetID ( primaryKey ), StudentName

StudentCourses : CourseID ( primaryKey ) , Student ( primaryKey )

 

group by 예제 1.

모든 학생의 목록을 뽑고 각 학생이 얼마나 많은 강의를 수강하고 있는지 확인하라

 

이 문제를 보게되면 우리는 다음과 같은 sql 을 작성할 수 있겠다.

 

select Student.studentName , count(*)

from student s

inner join studentCourses sc on s.studentID = sc.StudentId

group by s.studentID

 

이렇게 되면 3가지의 문제점이 있다 발견하였는가 ?

 

• inner join 을 하기 때문에 강의를 수강하지 않는 학생은 제외된다.
  
  • left join 으로 바꾸자 ( outer join )
• left join 을 하더라도 count ( * ) 는 group 내에 존재하는 아이템의 개수를 센다. 따라서 강의를 수강하지 않는 학생의 경우에도 1로 계산되는 문제가 존재한다.
  • count( StudentCourse.CourseID ) 와 같이 그룹 내의 CourseID 수를 세도록 변경해야 한다.
• Students.studentID 를 사용해서 그룹을 만들었지만 , 여전히 한 그룹 안에 여러개의 StudentName 이 존재한다. 데이터베이스가 어떤 StudentName 을 반환해야 하는지 어떻게 알 수 있을까? 모두가 같은 값을 가진다 해도 데이터베이스는 그 사실을 이해하지 못할 것이다. 
  • first(Students.StudentName) 과 같은 집계 합수를 사용할 필요가 있다.

 

해결책 1. inner query ? 를 사용한다.

select StudentName , Students.StudentID, CNT
From (
	Select Student.StudentID, count(StudentCourse.studentID) as CNT
    from Student 
    LEFT JOIN StudentCourse on Student.studentID = StudentCourse.studentID
    group by student.studentID
) as T 
inner join Student on Student.studentID = T.studentID

 

해결책 2. group by 에 student.studentName 을 추가한다.

 

해결책 3. 집계함수를 사용한다.

 

Select max(StudentName) ,Student.StudentID, count(StudentCourse.studentID) as CNT
    from Student 
    LEFT JOIN StudentCourse on Student.studentID = StudentCourse.studentID
    group by student.studentID

group by 예제 2.

모든 교사 목록과 각 교사가 가르치는 학생 수를 구하는 질의문을 작성해 보자.

 

select TeacherID, count(StudentCourse.CourseID) as Number
from courses 
inner join studentCourse on course.courseID = studentCourse.courseID
group by courses.TeacherID

 

inner join 을 하게 되면 강의를 하지 않는 교사는 목록에 포함되지 않는다. 

 

해결책

select TeacherName, TeacherID, ifNull(count(StudentSize.Number) , 0)
from Teacher 
left join 
	( select TeacherID, count(StudentCourse.courseID) as Number
      from Course
      inner join StudentCourse on Course.courseID = StudentCourse.couresID
      group by coures.TeacherID
     ) as StudnetSize on Teacher.teacherID = StudentSize.teacherID
 order by StudentSizd.Number desc