[SQL]데이터 요약값 찾기(MAX, MIN, AVG, SUM, COUNT)

- PC 또는 태블릿 환경에서 보는 것을 추천드립니다.

- 이전 과정의 연습문제를 확인하고 싶으면 다음을 참고하시기 바랍니다.

[SQL]초보자를 위한 SQL 200제 문제풀이 모음

 

036 최댓값 출력하기(MAX)

데이터에서 최대값을 출력하는 경우 MAX 함수를 사용합니다.

EX) 사원번호 중 최대 값

SELECT MAX(EMPNO)
FROM EMP;

Q. 사원테이블에서 최대 월급을 출력하라

사원 테이블(EMP)에서 월급(SAL)의 최대 값을 구해야 하기 때문에 MAX 함수를 사용한다.

SELECT MAX(SAL)
FROM EMP;

 

그룹 별 최대 값 출력하기

EX) 직업이 SALESMAN인 사원들 중에서 최대 월급을 직업과 같이 출력해야 하는 경우

문제에서 조건이 직업이 SALESMAN 인 경우 이기 때문에 WHERE 절에 JOB = 'SALESMAN'을 작성하여 다음과 같이 작성할 것이라 생각할 수 있다.

SELECT JOB, MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN';

하지만 위와 같이 입력할 경우 SQL은 아래와 같은 ERROR를 출력하는 것을 확인할 수 있다.

ORA-00937 : NOT A SINGLE-GROUP GROUP FUNCTION

이는 단일 그룹의 그룹 함수가 아니라는 에러로, 이와 같은 에러가 발생하는 이유는 SELECT 절에 있는 JOB 컬럼의 값은 여러 개의 행이 출력되려고 하는데 MAX(SAL) 값은 하나가 출력되려고 하기 때문이다

JOB 컬럼을 SELECT한 경우 여러행이 출력되지만, MAX(SAL)의 경우 단일행이 출력된다. 따라서 이와 같은 경우 GROUP BY절이 필요하다.

GROUP BY 절은 데이터를 GROUPING 하는 역할을 한다. 따라서 위와 같이 그룹 함수 문제에서는 JOB을 기준으로 GROUPING 하고 난 후, WHERE 절의 조건을 만족하는 직업이 SALESMAN인 사원들 중에서의 결과가 출력된다. 

SELECT JOB, MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN'
GROUP BY JOB;

위의 SQL 실행 순서는 다음과 같다.

코딩 순서	SQL	실행순서
1	SELECT JOB, MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN' GROUP BY JOB	4
2		1
3		2
4		3

 

Q. 부서 번호와 부서 번호 별 최대 월급을 출력하라

부서 번호(DEPTNO) 별 최대 월급(SAL)이기 때문에 부서 번호로 그룹핑을 하고, MAX함수를 이용하여 최댓값을 구한다.  그룹핑을 위해서 GROUP BY 절을 작성한다.

SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO;

 

037 최솟값 출력하기(MIN)

데이터에서 최솟값을 출력하는 경우 MIN 함수를 사용합니다.

EX) 부서 번호가 10번인 사원들 중 최소 월급

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 10;

 

위의 SQL은 FROM 절에서 사원 테이블을 가져오고, WHERE 절을 실행하여 부서 번호가 10번인 사원들로 데이터를 제한합니다. 마지막으로 SELECT 절을 실행하면서 그중 최소 월급을 출력합니다.

코딩 순서	SQL	실행순서
1	SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10;	3
2		1
3		2

 

Q. 직업이 SALESMAN인 사원들 중 최소 월급을 출력하라

'직업(JOB)이 SALESMAN인 사원'이라는 조건이 있으므로 WHERE 절에는 JOB = 'SALESMAN'이라는 조건을 입력하고,  MIN함수를 사용하여 그들 중 최소 월급을 출력합니다.

SELECT MIN(SAL)
FROM EMP
WHERE JOB = 'SALESMAN';

 

ORDER BY를 이용하여 정렬하여 출력하기

SQL에서 ORDER BY절은 출력값을 오름차순, 또는 내림차순(DESC)으로 정렬하기 위해 사용된다. ORDER BY 절의 위치는 항상 맨 마지막에 작성되고 실행 또한 맨 마지막에 실행된다. 이와 같은 특징으로 SELECT에서 정한 별칭을 사용할 수 있다는 장점이 있다.

EX ) 직업과 직업별 최소 월급을 출력하는데, ORDER BY절을 사용하여 최소 월급이 높은 것부터 출력하라

직업별 최소월급을 출력해야하기 때문에 GROUP BY절에 JOB을 입력해야하며, 최소 월급이 높은 것 부터, 즉 내림차순으로 출력해야하기 때문에 ORDER BY MIN(SAL) 과 함께 DESC 옵션을 입력합니다.

SELECT JOB, MIN(SAL) AS 최소값
FROM EMP
GROUP BY JOB
ORDER BY 최소값 DESC;

N O T E  WHERE절에서 주의해야 할 점

여기서 주의해야 할 점은 그룹함수의 특징 중 WHERE절의 조건이 거짓이어도 항상 출력한다는 것입니다. (즉 ERROR로 인해 실행이 안되진 않는 다는 의미) 이는 다음 SQL의 결과를 통해 확인할 수 있습니다.

-- 예제
SELECT MIN(SAL)
FROM EMP
WHERE 1 = 2;

WHERE절에 1 = 2 라는 조건이 거짓이지만 실행이 되는 것을 확인할 수 있습니다. 다만 결과가 NULL이기 때문에 NULL로 출력되었는지 확인할 수 있는 NVL 함수를 통해 NULL여부를 검증하고, 다른 값으로 치환하여 출력할 수 있습니다.

SELECT NVL(MIN(SAL),0)
FROM EMP
WHERE 1 = 2;

 

038 평균값 출력하기(AVERAGE : AVG)

데이터의 평균값을 출력하는 경우 AVG 함수를 사용합니다.

EX ) 사원 테이블의 평균 월급을 소수 첫 번째 자리에서 반올림하여 구하는 경우

SELECT ROUND(AVG(SAL),0)
FROM EMP;

Q. 사원 테이블의 평균 커미션을 출력하시오

커미션(COMM)의 평균값을 출력하므로 AVG 함수를 이용합니다.

SELECT AVG(COMM)
FROM EMP;

 

위의 커미션의 평균을 구하는 문제에서 확인해야 할 점은 COMM 컬럼의 경우 NULL값이 있는 컬럼이라는 점이다.

 

COMM 컬럼만 출력해서 보면 왼쪽의 사진과 같이 NULL이 포함되어 있는 것을 확인할 수 있다.

때문에 위 문제에서 구한 평균값은 NULL이 아닌 4개의 평균을 구한것 이다.

그룹함수는 NULL값을 무시하기 때문에 다음과 같이 NULL값 대신 0으로 치환해 평균을 계산할 경우 계산결과가 달라지는 것을 확인할 수 있다.

 

 

SELECT AVG(NVL(COMM,0))
FROM EMP;

 

그룹함수는 NULL값을 무시하므로 NVL 함수를 사용 안 한 쿼리는 커미션에 대해 계산할 때 NULL 값은 제외하고 NULL이 아닌 숫자들만 계산합니다. 그러나 NVL 함수를 사용해서 커미션의 NULL을 0으로 변경하면 계산을 수행할 때 0이 연산에 포함되어 시간이 더 걸리게 됩니다.

따라서 원하는 값에 따라, 또는 원하는 정보에 따라 다르게 작성되겠지만, 같은 값을 출력하는 계산에 대해서는 NVL 함수를 사용하지 않는 것이 성능이 더 좋다고 할 수 있겠습니다.

 

039 총합 출력하기(SUM)

숫자 데이터의 합계를 출력하는 경우 SUM 함수를 사용합니다.

EX ) 부서번호(DEPTNO)와 부서 번호 별 토탈 월급을 출력하는 경우

부서 번호를 기준으로 그룹핑 후, 월급의 총 합계를 구하기 위해 GROUP BY절에 DEPTNO를 입력하고, SELECT 절에 합계를 구하는 SUM(SAL)을 입력합니다.

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;

Q. 직업과 직업별 토탈 월급을 출력하는데 직업별 토탈 월급이 높은 것부터 출력하시오

직업을 기준으로 그룹핑 후, 월급의 총 합계를 구하기 위해 GROUP BY절에 JOB를 입력하고, SELECT 절에 합계를 구하는 SUM(SAL)을 입력합니다. 또한 월급의 합계가 높은 것 부터, 즉 내림차순으로 정렬하기 위해 ORDER BY절에 SUM(SAL)과 함께 DESC 옵션을 입력합니다.

SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
ORDER BY SUM(SAL) DESC;

 

EX ) 직업과 직업별 토탈 월급을 출력하는데 직업별 토탈 월급이 4000 이상인 것만 출력하는 경우

직업별 월급의 총 합계가 4000 이상인 조건이 포함되어 있기 때문에 WHERE절에 'SUM(SAL) >= 4000' 과 같은 조건을 활용하여 다음과 같은 쿼리를 작성할 수도 있다. 

SELECT JOB, SUM(SAL)
FROM EMP
WHERE SUM(SAL) >= 4000
GROUP BY JOB;

하지만 위 쿼리를 실행할 경우 다음과 같은 ERROR가 발생한다.

ORA-00934 GROUP FUNCTION IS NOT ALLOWED HERE

그룹 함수는 허가되지 않았다는 이 오류는 위와 같이 WHERE절에 그룹함수를 사용해 조건을 주면 그룹함수는 허가 되지 않기 때문입니다. 즉, 그룹함수에 대해 조건을 줄 경우 WHERE 절을 사용할 수 없습니다. 여기서 그룹함수에 대해 조건을 줄 때는 WHERE 절 대신 HAVING절을 사용해야 합니다.

SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) >= 4000;

Q. 직업과 직업별 토탈 월급을 출력하는데 직업에서 SALESMAN은 제외하고, 직업별 토탈 월급이 4000 이상인 사원들만 출력하시오

직업에서 SALESMAN을 제외하는 조건은 SELECT 조건이므로 WHERE절에, 직업별 월급의 총합이 4000 이상인 조건은 그룹 함수에 대한 조건이므로 HAVING절에 작성합니다.

SELECT → WHERE, ORDER BY → HAVING

SELECT JOB, SUM(SAL)
FROM EMP
WHERE JOB =! 'SALESMAN'
GROUP BY JOB
HAVING SUM(SAL) >= 4000;

 

SELECT 문의 6가지 절을 다 사용해 SQL을 작성하면 코딩 순서와 실행 순서는 다음과 같습니다.

코딩 순서	SQL	실행 순서
1	SELECT JOB, SUM(SAL)	5
2	FROM EMP	1
3	WHERE JOB =! 'SALESMAN'	2
4	GROUP BY JOB	3
5	HAVING SUM(SAL) >= 4000	4
6	ORDER BY SUM(SAL) DESC;	6

 

040 건수 출력하기(COUNT)

데이터의 건수를 출력하는 경우 COUNT 함수를 사용합니다. 

EX ) 사원테이블 전체 사원수를 출력하는 경우

1) EMPNO를 이용하는 방법

SELECT COUNT(EMPNO)
FROM EMP;

COUNT(EMPNO)는 EMPNO의 개수를 COUNT 함으로써 전체 사원의 수를 출력하는 방법입니다. 사원수를 검색할때 EMPNO를 이용해 카운팅 한 이유는 고유한 값을 가지는 컬럼이기 때문입니다.

2) 별표(*)를 이용하는 방법

SELECT COUNT(*)
FROM EMP;

COUNT(*)는 전체 행을 하나씩 카운트 해 전체 사원의 수를 출력하는 방법입니다.

두 방법의 결과는 14로 동일

 

NULL이 포함된 컬럼을 COUNT 하는 경우

EMP 테이블에서 NULL이 포함된 커미션 컬럼을 COUNT 할 경우 다음과 같은 결과가 출력된다.

SELECT COUNT(COMM)
FROM EMP;

이처럼 커미션이 NULL이 아닌 사원의 수, 즉 '4'라는 결과가 나오는 이유는 COUNT와 같은 그룹 함수는 NULL값을 무시하기 때문입니다. 그러므로 그룹 함수로 SQL을 작성할 때는 NULL 값을 연산에 포함시키지 않아 NULL의  포함 여부에 따라 연산 결과가 달라질 수 있어 이를 염두하고 작성해야합니다. (NULL을 포함 할 경우 NVL 함수를 사용하여 결과를 출력합니다.)