SQL 문법 MariaDB (COUNT, 집계함수, SUM, AVG, MIN, MAX, GROUP BY, HAVING, 연습문제)

1. COUNT

 - COUNT는 개수를 세는 기능을 하는데, 개수를 조사할 필드명을 전달하는데 * 지정하면 필드에 상관없이

   조건 맞는 개수 리턴

SELECT COUNT(*) FROM tstaff;

# 별명(Alias)을 부여하면 결과셋에 이름을 표시
SELECT COUNT(*) AS "총 직원수" FROM tstaff;

# WHERE 절을 붙이면 조건에 맞는 레코드의 개수를 구함
SELECT COUNT(*) FROM tstaff WHERE salary >= 400;

# 조건에 맞는 레코드가 없어도 결과값은 역시 하나
SELECT COUNT(*) FROM tstaff WHERE salary >= 10000;

# COUNT(*)로 조사한 개수에 해당하는 레코드를 알고 싶으면 원하는 필드명 적음
SELECT COUNT(NAME) FROM tstaff;
SELECT COUNT(depart) FROM tstaff;

# 중복 부서를 제외하고 부서의 종류가 몇 개인지 알고 싶으면 필드명 앞에
# DISTINT 키워드를 붙임. 중복을 제거하고 3개의 부서가 있다고 출력
SELECT COUNT(score) FROM tstaff;

# COUNT 함수는 '필드값이 제대로 들어 있는 레코드의 개수만 구하며' 필드값이 NULL인 레코드는
# 개수에서 제외. name이나 depart는 NULL이 없어 전체 직원수와 같지만, score 필드는 NULL 값이 있음
SELECT COUNT(*) - COUNT(score) FROM tstaff;
SELECT COUNT(*) FROM tstaff WHERE score IS NULL;

 

2. 연습문제

# 1. 실적 없는 두 직원이 누구인지 목록을 출력하는 쿼리를 작성하라
SELECT NAME FROM tstaff WHERE score IS NULL;
# 2. 성취도가 80점 이상인 직원이 몇 명이나 되는지 조사하라
SELECT COUNT(*) FROM tstaff WHERE score >=80;

 

3. 합계와 평균

함수	설명
SUM	총합을 구함
AVG	평균을 구함
MIN	최소값을 구함
MAX	최대값을 구함
STDDEV	표준편차을 구함
VARIANCE	분산을 구함

 

# 도시 목록에서 인구의 총합과 평균을 구함
SELECT SUM(popu), AVG(popu) FROM tcity;

# 면적의 최소값과 최대값을 구함
SELECT MIN(AREA), MAX(AREA) FROM tcity;

# WHERE 절을 붙이면 조건을 만족하는 레코드에 대해서만 집계를 출력
SELECT SUM(score), AVG(score) FROM tstaff WHERE depart = '인사과';
SELECT MIN(salary), MAX(salary) FROM tstaff WHERE depart = '영업부';

# 문자열끼리는 총합, 평균은 안되지만 사전순으로 비교하여 MIX, MAX 사용 가능
SELECT MIN(NAME) FROM tstaff;

# 인구가 제일 많은 서울의 값이 나올 것 같지만, 서울의 인구수와 부산이 나옴
# 집계함수와 일반필드는 같이 사용하면 안됨
SELECT MAX(popu), NAME FROM tcity;

 

4. 연습문제 2

# 1. 여직원 중 최고 월급은 얼마인지 조사하라
SELECT MAX(salary) FROM tstaff WHERE gender = '여';
SELECT salary FROM tstaff WHERE gender = '여' ORDER BY salary DESC LIMIT 1;
# 2. 총무부 직원이 최초로 입사한 날짜를 구하라
SELECT MIN(joindate) FROM tstaff WHERE depart = '총무부';
SELECT joindate FROM tstaff WHERE depart = '총무부' ORDER BY joindate LIMIT 1;

 

5. 집계함수와 NULL

 - 모든 집계함수는 NULL을 무시하고 통계를 계산

 - 단, 예외적으로 레코드 개수를 세는 COUNT(*)는 NULL도 포함하지만, 인수로 필드를 지정하면 NULL을 세지 않음

# 평균은 총합을 개수로 나누어서 구함. 두 명령은 동일한 결과를 보여줌.
# AVG 함수는 NULL값을 제외하고 계산을 하지만, COUNT(*)의 경우 NULL값도 포함
# SUM(score)/COUNT(score)로 계산해야 정확
SELECT AVG(salary) FROM tstaff;
SELECT SUM(salary)/COUNT(*) FROM tstaff;

# COUNT(*)과 다른 집계함수의 경우, COUNT(*)는 없다는 뜻의 0을 리턴하지만,
# 다른 집계함수는 계산 대상이 없어서 존재하지 않는 0이 아니라 NULL을 반환
SELECT COUNT(*) FROM tstaff WHERE depart = '비서실';
SELECT MIN(salary) FROM tstaff WHERE depart = '비서실';

 

6. 연습문제 3

SELECT AVG(`단가`) FROM `제품`;
SELECT SUM(`재고량`) FROM `제품` WHERE `제조업체` = '한빛제과';
SELECT COUNT(*) AS '고객수' FROM `고객`;
SELECT COUNT(DISTINCT `제조업체`) AS '제조업체 수' FROM `제품`;

 

7. GROUP BY

 - 기준이 되는 필드를 뒤에 적어주면 기준 필드가 같은 레코드를 모아 통계값을 구함

 - 기준 필드는 집계 함수와 같이 쓸 수 있어서 목록도 보기 좋게 출력 가능

# 도시 목록에서 지역별 인구수를 구하라
SELECT region, SUM(popu) FROM tcity GROUP BY region;

# 도시 목록에서 지역별 평균 면적을 구하라
SELECT region, AVG(AREA) FROM tcity GROUP BY region;

# 각 부서별 남자직원의 평균 성취도를 조사하라
SELECT depart, gender, AVG(score) FROM tstaff where gender = '남' GROUP BY depart;

# 직급별 여직원의 수를 조사하라
SELECT grade, gender, COUNT(*) FROM tstaff WHERE gender = '여' GROUP BY grade;

 

8. 기준 필드

 - GROUP BY의 기준 필드는 중복 값이 있을 때나 의미

 - 고유한 값을 가지는 필드(이름, ID값 등)는 그룹핑 기준으로 부적합하며, 구분이나 분류 필드가 적합

 - 기준 필드를 콤마로 구분하여 두 개 이상도 쓸 수 있음. 

 - 첫 번째 기준으로 그룹을 나누고, 그 그룹 내에서 다시 두 번째 기준으로 그룹을 나눔

 - 기본 형식 : SELECT 기준필드, 집계함수() FROM 테이블 GROUP BY 기준필드;

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

# 순서는 ORDER BY로 조정 가능
SELECT depart, gender, COUNT(*) FROM tstaff GROUP BY depart, gender ORDER BY gender, depart;

 

9. HAVING

 - HAVING은 GROUP BY 다음에 오며 통계 결과 중 출력할 그룹의 조건을 지정

 - 즉 HAVING은 GROUP BY 문의 조건절

 - 출력 순서를 지정할 때는 ORDER BY 절을 뒤에 붙임

 - 순서 : GROUP BY > HAVING > ORDER BY (항상 제일 마지막)

 - WHERE와 HAVING은 적용 시점과 제한 대상이 다름

 

- 키워드 순서 : SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... (LIMIT) ..

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

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

# WHERE 절은 집계 전에 총무부를 제외하여 꼭 필요한 계산만 함
SELECT depart, MAX(salary) FROM tstaff WHERE depart IN ('인사과', '영업부') GROUP BY depart;

# HAVING 절은 모든 부서의 집계를 다 끝낸 후 총무부를 제거하는 식이라 출력하지도 않을 총무부의 집계까지 계산하여 비효율적
SELECT depart, MAX(salary) FROM tstaff GROUP BY depart HAVING depart IN ('인사과', '영업부');

# 인구수 50만명인 지역을 출력하라
SELECT region, MAX(AREA) FROM tcity WHERE popu >= 50 GROUP BY region;

# 평균 면적이 1000 이상인 지역 출력하라
SELECT region, AVG(AREA) AS avg_area FROM tcity GROUP BY region HAVING AVG(AREA) >= 1000;

 

10. 연습문제 4 

SELECT AVG(단가) FROM `제품`;
SELECT SUM(재고량) FROM `제품` WHERE 제조업체 = '한빛제과';
SELECT COUNT(*) AS '고객수' FROM `고객`;
SELECT COUNT(distinct 제조업체) AS '제조업체 수' FROM `제품`;

SELECT 주문제품, SUM(수량) AS 총주문수량 FROM `주문` GROUP BY 주문제품;

SELECT 제조업체, COUNT(제조업체) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체;

SELECT 제조업체, COUNT(제조업체) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체 HAVING 제품수 > 2;

SELECT 등급, COUNT(등급) AS 고객수, AVG(적립금) AS 평균적립금 FROM `고객` GROUP BY 등급 HAVING 평균적립금 >= 1000;

SELECT 주문제품, 주문고객, SUM(수량) AS 총주문수량  FROM `주문` GROUP BY 주문번호 ORDER BY 주문제품, 주문고객;