mysql - GROUP BY, HAVING 과 다양한 집계함수

GROUP BY

• 주로 그룹 별 데이터를 집계할때 사용하며, 엑셀의 피벗 시능과 유사하다.
• GROUP BY가 쓰인 쿼리의 SELECT 절에는 GROUP BY 대상 컬럼과 그룹 함수만 사용 가능하다.
• 칼럼번호(SELECT 절의 칼럼 이름 순서)로도 그룹화가 가능하다.

HAVING

• GROUP BY 절의 조건식으로 조건식이 참이 되는 그룹만을 가져온다.
• HAVING 절의 조건식에는 그룹함수를 활용한다.

예제 테이블

CREATE DATABASE pokemon;

SHOW DATABASES;

USE pokemon;

DROP TABLE mypokemon;
CREATE TABLE pokemon.mypokemon (
	number INT NOT NULL,
	name VARCHAR(1048) NOT NULL,
	height FLOAT NOT NULL,
	weight FLOAT NOT NULL,
	friendship FLOAT,
	type VARCHAR(1024) NOT NULL,
	PRIMARY KEY(number)
);

INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (10, 'caterpie', 0.3, 2.9, -1.455, bug);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (25, 'pikachu', 0.4, 6, 124.78, electric);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (26, 'raichu', 0.8, 30 ,30.289, electric);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (125, 'electabuzz', 1.1, 30, -10.67, electric);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (133, 'eevee', 0.3, 6.5, 15.988, normal);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (137, 'porygo', 0.8 ,36.5, -0.245, normal);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (152, 'chikorita', 0.9, 6.4, 67.164, grass);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (153, 'bayleef', 1.2, 15.8 ,9.756, grass);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (172, 'pichu', 0.3, 2, 872.1, electric);
INSERT INTO mypokemon (number, name, height, weight, friendship, type) 
	VALUES (470, 'leafeon', 1, 25.5, 3.42, grass);

SELECT * FROM pokemon.mypokemon;

 

예시 쿼리

SELECT type, COUNT(*), COUNT(1), AVG(height), MAX(weight) 
FROM pokemon.mypokemon
GROUP BY type;

 

SELECT type, COUNT(*), COUNT(1), AVG(height), MAX(weight) 
FROM pokemon.mypokemon
GROUP BY type
HAVING COUNT(1) >= 2
ORDER BY 1 ASC;