[PHP] MySQL 집계함수 sum, avg, max, min

124_PHP MySQL 집계함수

[요약]

• 최대값, 최소값 등을 확인하는 집계함수 명령문 max, min 등에 대해 알아보자. 

---

집계함수는 레코드의 수, 값들의 합계, 평균, 최대값, 최소값을 구하는 함수이다.

집계함수를 학습하기 위해 학생들의 과목별 성적의 합계, 평균값 등을 구해보자.

 

성적 데이터가 없기 때문에 만들어야 한다.

학생들의 명단은 myMember 테이블을 사용하며, 성적 데이터를 저장할 테이블을 새로 생성한다.

---

[테이블 정보]

테이블 이름 : schoolRecord

필등 :
학생번호 myMemberID 와 값이 일치하게 만듬
클래스(소속 반)
영어 점수
수학 점수
과학 점수
일본어 점수
코딩 점수

schoolRecord 테이블 생성 쿼리문은 다음은 같습니다.

CREATE TABLE schoolRecord (
schoolRecordID int(10) unsigned AUTO_INCREMENT COMMENT '학생 번호',
myMemberID int unsigned NOT NULL COMMENT '회원번호',
class tinyint unsigned COMMENT '소속 클래스(반)',
english tinyint unsigned NOT NULL COMMENT '영어 점수',
math tinyint unsigned NOT NULL COMMENT '수학 점수',
science tinyint unsigned NOT NULL COMMENT '과학 점수',
japanese tinyint unsigned NOT NULL COMMENT '일본어 점수',
coding tinyint unsigned NOT NULL COMMENT '코딩 점수',
PRIMARY KEY (schoolRecordID)
) CHARSET=ut8 comment='성적 정보';

 

다음은 schoolRecord 테이블을 생성하는 예제이다.

[예제: 127-1_createTb.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $sql = "CREATE TABLE schoolRecord (";
    $sql .= "schoolRecordID int(10) unsigned AUTO_INCREMENT COMMENT '학생 번호',";
    $sql .= "myMemberID int unsigned NOT NULL COMMENT '회원번호',";
    $sql .= "class tinyint unsigned COMMENT '소속 클래스(반)',";
    $sql .= "english tinyint unsigned NOT NULL COMMENT '영어 점수',";
    $sql .= "math tinyint unsigned NOT NULL COMMENT '수학 점수',";
    $sql .= "science tinyint unsigned NOT NULL COMMENT '과학 점수',";
    $sql .= "japanese tinyint unsigned NOT NULL COMMENT '일본어 점수',";
    $sql .= "coding tinyint unsigned NOT NULL COMMENT '코딩 점수',";
    $sql .= "PRIMARY KEY (schoolRecordID)";
    $sql .= ") CHARSET=utf8 COMMENT='성적 정보';";

    $result = $dbConnect->query($sql);

    if ($result) {
      echo "테이블 생성 완료";
    } else {
      echo "테이블 생성 실패";
    }
?>

실행결과

터미널이나 phpMyAdmin 에서 SHOW TABLES 명령문으로 테이블을 확인할 수 있다.

 

schoolRecord 테이블을 생성했으므로, 학생들의 성적 정보를 입력해야 한다.

다음은 점수 정보를 schoolRecord 테이블에 입력하는 예제이다.

[예제: 127-2_insert.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $score = array();
    $score[0] = [1, 1, 90, 80, 90, 90, 100];
    $score[1] = [2, 1, 85, 90, 80, 80, 100];
    $score[2] = [3, 2, 100, 90, 70, 70, 100];
    $score[3] = [4, 2, 90, 86, 90, 70, 100];

    $cnt = 0;

    foreach($score as $s) {
      $sql = "INSERT INTO schoolRecord";
      $sql .= "(myMemberID, class, english, math, science, japanese, coding)";
      $sql .= "VALUES({$s[0]}, {$s[1]}, {$s[2]}, {$s[3]}, {$s[4]}, {$s[5]}, {$s[6]})";

      $result = $dbConnect->query($sql);
      $cnt++;

      if($result) {
        echo $cnt.' 데이터 입력 성공'.'<br>';
      } else {
        echo $cnt.' 데이터 입력 실패'.'<br>';
      }
    }
?>

실행결과

자, 이제 집계함수를 사용할 데이터도 만들었다.

 

[집계함수의 종류]

종류	의미
count(필드명)	레코드의 개수를 표시(값이 null인 경우 포함되지 않음)
count(*)	레코드의 개수를 표시 (null을 포함)
sum(필드명)	필드의 값의 합계를 표시
avg(필드명)	필드의 값의 평균을 표시
max(필드명)	필드의 값의 최대값을 표시
min(필드명)	필드의 값의 최소값을 표시

 

다음은 count(필드명)를 활용한 쿼리문이다.

SELECT count(class) FROM schoolRecord;

 

다음은 class 필드를 기준으로 하여 count()를 사용한 후 class 필드의 한 값을 null로 변경하고 다시 count()를 사용하여 이전과 이후의 차이를 확인하는 예제이다.

[예제: 127-3_count.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    function schoolRecord() {
      global $dbConnect;
      $sql = "SELECT count(class) FROM schoolRecord";
      $result = $dbConnect->query($sql);  // 쿼리 송신
      $classInfo = $result->fetch_array(MYSQLI_ASSOC);
      echo 'class 필드를 기준으로 한 레코드 수 : ';
      echo $classInfo['count(class)'];
      echo '<br>';
    }

    schoolRecord();

    // schoolRecordID가 1인 레코드의 class 필드 값을 NULL로 변경
    $sql = 'UPDATE schoolRecord SET class = NULL WHERE schoolRecordID = 1';
    $dbConnect->query($sql);

    schoolRecord();
?>

실행결과

 

다음은 count(*)를 사용해서 NULL인 데이터의 수도 포함한 결과를 보이는지 확인하는 예제이다.

[예제: 127-4_countAll.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $sql = "SELECT count(*) FROM schoolRecord";
    $result = $dbConnect->query($sql);  // 쿼리 송신
    $classInfo = $result->fetch_array(MYSQLI_ASSOC);
    echo '레코드 수 : ' . $classInfo['count(*)'];
?>

실행결과

---

어떤 필드의 값을 모두 합한 값을 보려면 sum 함수를 사용한다.

다음은 sum() 함수를 사용하여 영어 점수를 합한 값을 구하는 쿼리문이다.

SELECT sum(english) FROM schoolRecord;

 

다음은 위의 쿼리문을 사용하여 영어 점수의 합계를 구하는 예제이다.

[예제: 127-5_sum.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $sql = "SELECT sum(english) FROM schoolRecord";
    $result = $dbConnect->query($sql);
    $score = $result->fetch_array(MYSQLI_ASSOC);
    echo ' 영어 점수 합계 : ' . $score['sum(english)'];
?>

실행결과

---

avg() 함수를 사용하면 특정 필드의 평균값을 구할 수 있다.

다음은 avg() 함수를 사용한 쿼리문이다.

SELECT avg(math) FROM schoolRecord;

 

다음은 위의 쿼리문을 사용하여 수학점수의 평균을 구하는 예제이다.

[예제: 127-6_avg.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $sql = "SELECT avg(math) FROM schoolRecord;";
    $result = $dbConnect->query($sql);
    $score = $result->fetch_array(MYSQLI_ASSOC);
    echo '수학점수 평균 : ' . $score['avg(math)'];
?>

실행결과

---

max() 함수를 사용하면 특정 필드의 최대값을 구할 수 있다.

다음은 max() 함수를 사용한 쿼리문이다.

SELECT max(japanese) FROM schoolRecord;

 

다음은 위의 쿼리문을 사용하여 일본어 점수의 최대값을 구하는 예제이다.

[예제: 127-7_max.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $sql = "SELECT max(japanese) FROM schoolRecord;";
    $result = $dbConnect->query($sql);
    $score = $result->fetch_array(MYSQLI_ASSOC);
    echo '가장 높은 일본어 점수 : ' . $score['max(japanese)'];
?>

실행결과

---

min() 함수를 사용하면 특정 필드의 최소값을 구할 수 있다.

다음은 min() 함수를 사용한 쿼리이다.

SELECT min(math) FROM schoolRecord;

 

다음은 위의 쿼리문을 사용하여 수학점수의 최소값을 구하는 예제이다.

[예제: 127-8_min.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connectDB.php';

    $sql = "SELECT min(math) FROM schoolRecord;";
    $result = $dbConnect->query($sql);
    $score = $result->fetch_array(MYSQLI_ASSOC);
    echo '가장 낮은 수학 점수 : ' . $score['min(math)'];
?>

실행결과

 

 

 

---

 

 

 

초보자를 위한 PHP 200제
김태영 지음 | 정보문화사