MySQL - GROUP BY(그룹핑) 알아보기

MySQL - GROUP BY(그룹핑) 알아보기

 

 

이번 포스팅에서는 GROUP BY(그룹핑)에 대해서 알아보도록 하겠습니다.

 

 

 

 

 

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를 통해 컬럼을 그룹하면 같은 값을 가지고 있는 데이터를 기준으로 그룹하게 되고, 예로 회원테이블의 남,여 성별이 몇명인지, 회원나이 평균나이, 최소나이, 최대나이 등등 조회할때 가장 많이 사용됩니다.

 

 

 

 

 

MySQL - GROUP BY(그룹핑) 사용법

 

 

 

 

 

1. GROUP BY 사용법

 

사용법 : 
	SELECT  [컬럼명]
	           [컬럼명]
	 FROM  [테이블]
	GROUP BY [그룹할 컬럼명];

 

 

 

 

2. GROUP BY 사용법

 

SELECT member_sex  FROM member_table group by member_sex;

 

 

 

 

3. GROUP BY 출력결과

 

+------------+
| member_sex |
+------------+
| 남자       |
| 여자       |
+------------+

 

겉으로 볼때 조회된 데이터는 2건이지만 그룹한 컬럼의 값은 각각 하나의 그룹으로 나태내고 있습니다

 

 

 

 

 

MySQL - GROUP BY(그룹핑) 조회 결과 구조

 

 

SELECT member_sex  FROM member_table group by member_sex;
+------------+
| member_sex |
+------------+
| 남자       |
| 여자       |
+------------+

 

그룹하여 조회할 경우 남자, 여자 이렇게 2개의 데이터가 있는것처럼 보이게 됩니다

 

 

 

 

그러나 내부적으로는 완전히 다르며, member_sex 컬럼의 "남자"는 남자의 모든데이터가 있고, member_sex 컬럼의 "여자"는 여자의 모든 데이터가 있게 됩니다.