MySQL로 연도별 출석·결석 학생 수 집계하기
MySQL에서 특정 연도의 출석(Present) 학생과 결석(Absent) 학생 수를 각각 계산하려면 IF() 함수와 SUM() 집계 함수를 함께 사용하면 됩니다. IF() 함수는 조건을 만족하면 1, 그렇지 않으면 0을 반환하므로, 이 값을 SUM()으로 합산하면 원하는 개수를 손쉽게 구할 수 있습니다.
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table DemoTable1617
-> (
-> Attendance varchar(20),
-> CurrentYear int
-> );
Query OK, 0 rows affected (0.48 sec)
샘플 데이터 삽입하기
insert 명령을 사용하여 테이블에 몇 개의 레코드를 삽입합니다.
mysql> insert into DemoTable1617 values('Present',2019);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1617 values('Absent',2019);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1617 values('Absent',2017);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1617 values('Present',2019);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1617 values('Present',2018);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1617 values('Present',2019);
Query OK, 1 row affected (0.14 sec)저장된 데이터 확인하기
select 문을 사용하여 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable1617;
위 쿼리는 다음과 같은 결과를 출력합니다.
+------------+-------------+
| Attendance | CurrentYear |
+------------+-------------+
| Present | 2019 |
| Absent | 2019 |
| Absent | 2017 |
| Present | 2019 |
| Present | 2018 |
| Present | 2019 |
+------------+-------------+
6 rows in set (0.00 sec)
출석·결석 학생 수를 계산하는 쿼리
다음은 특정 연도(여기서는 2019년)의 출석 학생 수와 결석 학생 수를 동시에 구하는 쿼리입니다.
mysql> select sum(if(Attendance='Present',1,0)) as Present ,
-> sum(if(Attendance='Absent',1,0)) as Absent
-> from DemoTable1617
-> where CurrentYear LIKE '2019%';
위 쿼리는 다음과 같은 결과를 출력합니다.
+---------+--------+
| Present | Absent |
+---------+--------+
| 3 | 1 |
+---------+--------+
1 row in set (0.00 sec)
쿼리 동작 원리 살펴보기
이 쿼리가 어떻게 작동하는지 단계별로 살펴보겠습니다.
1. WHERE 절로 데이터 필터링: where CurrentYear LIKE '2019%' 조건을 통해 2019년에 해당하는 레코드만 선택됩니다. 이 예제에서는 4개의 레코드가 해당됩니다.
2. IF() 함수로 조건 판별: if(Attendance='Present',1,0)은 출석 상태가 'Present'이면 1을, 아니면 0을 반환합니다. 결석 카운트도 같은 방식으로 처리됩니다.
3. SUM()으로 최종 집계: 반환된 1과 0의 값들을 모두 더하면 각각의 개수가 됩니다. 결과적으로 2019년에는 출석 학생 3명, 결석 학생 1명임을 확인할 수 있습니다.
참고로 LIKE 대신 등호(=)를 사용하여 where CurrentYear = 2019와 같이 정확한 값 비교를 하는 것이 성능 면에서 더 효율적일 수 있습니다. 또한 MySQL 8.0 이상에서는 COUNT(CASE WHEN ... THEN 1 END) 형태나 COUNTIF 스타일의 조건부 집계로도 동일한 결과를 얻을 수 있습니다.