SQL 기초 - 5. 데이터 집계 (집계함수, 그룹핑)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 5. 데이터 집계 (집계함수, 그룹핑)

-- 전체 행의 갯수를 출력
SELECT COUNT(*) FROM tStaff;
SELECT COUNT(*) AS "총 직원수" FROM tStaff;

-- WHERE절로 조건을 줄 수 있다.
SELECT COUNT(*) FROM tStaff WHERE salary >= 400;

SELECT COUNT(*) FROM tStaff WHERE salary >= 10000;

SELECT name FROM tStaff WHERE salary >= 400;

-- 특정 열의 갯수를 셀 수 있다.
SELECT COUNT(name) FROM tStaff;
SELECT COUNT(depart) FROM tStaff;

-- 이때, DISTINCT를 넣으면 중복을 제거한다.
SELECT COUNT(DISTINCT depart) FROM tStaff;

SELECT COUNT(score) FROM tStaff;

SELECT COUNT(*) - COUNT(score) FROM tStaff;
SELECT COUNT(*) FROM tStaff WHERE score IS NULL;

-- SUM, MAX, MIN 등은 단독으로, 혹은 GROUP BY절과 함께 사용할 수 있다.
SELECT SUM(popu), AVG(popu) FROM tCity;

SELECT MIN(area), MAX(area) FROM tCity;

SELECT SUM(score), AVG(score) FROM tStaff WHERE depart = '인사과';
SELECT MIN(salary), MAX(salary) FROM tStaff WHERE depart = '영업부';

-- 문자열에 대해서는 SUM이 불가능하다.
SELECT SUM(name) FROM tStaff;		-- 에러

SELECT MIN(name) FROM tStaff;

SELECT MAX(popu), name FROM tCity;

SELECT AVG(salary) FROM tStaff;
SELECT SUM(salary)/COUNT(*) FROM tStaff;

SELECT AVG(score) FROM tStaff;
SELECT SUM(score)/COUNT(*) FROM tStaff;

SELECT COUNT(*) FROM tStaff WHERE depart = '비서실';
SELECT MAX(salary) FROM tStaff WHERE depart = '비서실';

-- 각 부서별로 평균값을 구하는 건 귀찮다.
SELECT '영업부', AVG(salary) FROM tStaff WHERE depart='영업부';
SELECT '총무부', AVG(salary) FROM tStaff WHERE depart='총무부';
SELECT '인사과', AVG(salary) FROM tStaff WHERE depart='인사과';

-- 대신 GROUP BY를 써서 한번에 계산하자.
SELECT depart, AVG(salary) FROM tStaff GROUP BY depart;

SELECT depart, COUNT(*), MAX(joindate), AVG(score) FROM tStaff GROUP BY depart; 

SELECT gender, AVG(salary) FROM tStaff GROUP BY gender;

SELECT name, SUM(salary) FROM tStaff GROUP BY name;

SELECT depart, gender, COUNT(*) FROM tStaff GROUP BY depart, gender;

SELECT gender, depart, COUNT(*) FROM tStaff GROUP BY gender, depart;

SELECT depart, gender, COUNT(*) FROM tStaff GROUP BY depart, gender 
ORDER BY depart, gender;

SELECT depart, salary FROM tStaff GROUP BY depart;

SELECT SUM(salary) FROM tStaff GROUP BY depart;

SELECT depart, SUM(salary) FROM tStaff;

SELECT depart, SUM(salary) FROM tStaff GROUP BY depart;
SELECT SUM(salary) FROM tStaff;

-- HAVING은 GROUP BY 이후에, WHERE은 GROUP BY 이전에 실행된다.
SELECT depart, AVG(salary) FROM tStaff GROUP BY depart HAVING AVG(salary) >= 340;

SELECT depart, AVG(salary) FROM tStaff GROUP BY depart HAVING AVG(salary) >= 340 
ORDER BY AVG(salary);

SELECT depart, AVG(salary) FROM tStaff WHERE salary > 300 GROUP BY depart ;

SELECT depart, AVG(salary) FROM tStaff WHERE salary > 300 
GROUP BY depart HAVING AVG(salary) >= 360 ORDER BY depart;

-- 두 가지 쿼리를 보며 WHERE와 HAVING의 실행 순서의 차이를 익혀두자.
SELECT depart, MAX(salary) FROM tStaff WHERE depart IN ('인사과', '영업부') GROUP BY depart;
SELECT depart, MAX(salary) FROM tStaff GROUP BY depart HAVING depart IN ('인사과', '영업부');