MySQL - GROUP BY와 집계함수

MySQL - GROUP BY와 집계함수

 

 

 

이번 포스팅에서는 이번포스팅에서는 GROUP BY에서 집계함수를 사용하는 방법에 대해서 알아보도록 하겠습니다.

 

 

 

 

이전글

 

 

 

 

https://server-talk.tistory.com/528 - MySQL - GROUP BY(그룹핑) 알아보기

 

 

https://server-talk.tistory.com/529 - MySQL - 집계함수 알아보기

 

 

 

 

 

MySQL - 조회 테이블 데이터 생성

 

 

 

 

 

1. 조회할 테이블 명세서 - 회원 테이블

 

 

 

 

 

2. 조회할 테이블 생성 - 회원 테이블

 

CREATE TABLE member_table (
  seq                   INT NOT NULL AUTO_INCREMENT,
  member_name     VARCHAR(4) NOT NULL,
  member_sex 	 ENUM('남자','여자') NOT NULL,
  member_address  VARCHAR(50) NOT NULL,
  member_age	 INT,
  member_birthday DATETIME NOT NULL,
  PRIMARY KEY (`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

 

3. 데이터 삽입 - 회원 테이블

 

INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '홍길동', '남자', '서울', 36, '1985-10-26');
INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '강감찬', '남자', '부산', 39, '1982-11-16');
INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '선덕', '여자', '인천', 33, '1989-2-10');
INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '홍길순', '여자', '광주', 42, '1979-11-4');
INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '세일러', '여자', '대구', 31, '1990-10-1');
INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '썸녀', '여자', '울산', 22, '2000-01-01');
INSERT INTO member_table (member_name, member_sex, member_address, member_age, member_birthday) VALUE( '이자겸', '남자', '전주', 20, '2002-01-01');

 

 

 

 

 

MySQL - GROUP BY에서 집계함수의 활용

 

 

이전 포스팅에서는 GROUP BY 절의 지정하면 컬럼명을 기준으로 그룹핑이 되게 됩니다 이때 GROUP BY의 지정된 컬럼명만 조회가 가능한데 집계함수를 이용하며 그룹핑 된 컬럼을 기준으로 건수, 평균, 합계 등등을 조회할 수 있습니다.

테이블 조회시 GROUP BY를 사용할 경우 GROUP BY애서 지정한 컬럼만 조회가 가능합니다 

 

 

 

MySQL - GROUP BY에서 집계함수의 사용법

 

 

 

 

 

1. GROUP BY에서 집계함수 사용법

 

사용법 : 
	SELECT  [컬럼명]
	           [집계함수]([컬럼명])
	 FROM  [테이블]
	GROUP BY [그룹할 컬럼명];

 

 

 

2. GROUP BY에서 집계함수 사용법

 

SELECT member_sex, avg(member_age) FROM member_table GROUP BY member_sex;
+------------+-----------------+
| member_sex | avg(member_age) |
+------------+-----------------+
| 남자       |         31.6667 |
| 여자       |         32.0000 |
+------------+-----------------+

 

GROUP BY 절에 입력한 컬럼의 값을 묶어서 그룹별로 평균을 구한 결과 입니다.