MySQL - 집계함수 알아보기

MySQL - 집계함수 알아보기

 

 

 

이번 포스팅에서는 MySQL에서 사용 되는 집계함수에 대해서 알아보도록 하겠습니다.

 

 

 

 

 

이전글

 

 

 

 

https://server-talk.tistory.com/528 - MySQL - 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 - 집계함수란?

 

 

집계함수란? SQL에서는 특정 컬럼을 기준으로 데이터의 건수, 합계, 평균 등등의 데이터를 조회할때 사용됩니다.

 

 

 

MySQL - 집계함수의 종류

 

 

집계함수의 종류
COUNT	선택된 행의 개수를 조회하는데 사용되는 함수 입니다.
AVG	선택된 데이터의 평균을 조회하는데 사용되는 함수 입니다.
MIN	선택된 데이터의 최소값을 조회하는데 사용되는 함수 입니다.
MAX	선택된 데이터의 최대값을 조회하는데 사용되는 함수 입니다.
SUM	선택됨 데이터의 합계를 조회하는데 사용되는 함수 입니다.

 

 

 

MySQL - 집계함수 사용법

 

 

 

 

 

1. MySQL 집계함수 - 건수조회 - COUNT

 

 

사용법 : SELECT COUNT(*) FROM [테이블명]

 

SELECT COUNT(*) FROM member_table;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

 

COUNT 함수는 특정 컬럼의 행의 개수를 세는 함수이며, COUNT(*)를 입력하면 테이블 컬럼의 있는 행의 개수를 조회하게 됩니다.

 

 

 

 

2. MySQL 집계함수 - 평균값 조회 - AVG

 

 

사용법 : SELECT AVG([컬럼명]) FROM [테이블명]

 

SELECT AVG(member_age) FROM member_table;
+-----------------+
| AVG(member_age) |
+-----------------+
|         31.8571 |
+-----------------+

 

AVG 함수는 특정 컬럼의 평균을 조회하는 함수입니다.

 

 

 

 

3. MySQL 집계함수 - 최소값 조회 - MIN

 

사용법 : SELECT MIN([컬럼명]) FROM [테이블명]

 

SELECT MIN(member_age) FROM member_table;
+-----------------+
| MIN(member_age) |
+-----------------+
|              20 |
+-----------------+

 

 

 

 

4. MySQL 집계함수 - 최대값 조회 - MAX

 

사용법 : SELECT MAX([컬럼명]) FROM [테이블명]

 

SELECT MAX(member_age) FROM member_table;
+-----------------+
| MAX(member_age) |
+-----------------+
|              42 |
+-----------------+

 

MAX 함수는 특정 컬럼의 최대값을 조회하는 함수입니다.

 

 

 

 

5. MySQL 집계함수 - 합계 조회 - SUM

 

사용법 : SELECT SUM([컬럼명]) FROM [테이블명]

 

SELECT SUM(member_age) FROM member_table;
+-----------------+
| SUM(member_age) |
+-----------------+
|             223 |
+-----------------+

 

SUM 함수는 특정 컬럼의 합계을 조회하는 함수입니다.