[Oracle] 집계 함수: count(), sum(), avg(), max(), min()

💡집계 함수

---

집계 함수(Aggregation Function)는 여러 행 또는 테이블 전체 행으로부터 결괏값을 반환하는 함수를 의미한다.

 

집계 함수 사용시 주의할 점

1. SQL Error [937] [42000]: ORA-00937: not a single-group group function

SELECT count(*) FROM tblInsa; --직원수
SELECT name FROM tblInsa; --직원명

SELECT count(*), name FROM tblInsa;

위와 같은 데이터가 있다는 가정 하에 직원들 이름과 총 직원수를 동시에 가져오려고 할 때, 에러가 발생했다. 에러는 싱글 그룹 함수가 아니라는 메시지가 출력되었다.

이런 에러가 발생하는 이유는 컬럼 리스트에서는 집계 함수(count)와 일반 컬럼(name)을 동시에 사용할 수 없기 때문이다.

 

컬럼 리스트는 집계 함수와 일반 컬럼을 동시에 사용할 수 없다.

그렇다면 왜 동시에 사용하지 못하는 걸까?🤔
만약 이걸 되게 하려면 앞에 있는 컬럼을 계속 반복시키거나 병합시켜야 하는데, Oracle은 셀 병합 같은 기능이 없으므로 컬럼을 반복시킬 수밖에 없다.
하지만 컬럼을 반복하는 것은 말이 되지 않는다. 인스턴스 값은 각자 개인이 갖는 값인데, 60은 한 명이 만들어낸 값이 아니라 공용으로 만들어진 값이기 때문이다.
60은 자바로 따지면 static이다. 집합의 데이터와 개인의 데이터를 하나의 테이블에 담을 수 있는 방법이 없기 때문에 물리적인 표현은 가능하지만 실제로 작업은 안 된다는 것이다. 그래서 이러한 요구사항은 구현하는 게 불가능하다. 이를 어떻게든 해야 한다면, 동시에는 안 되고 따로 가져오는 방법밖에 없다. 그러니 억지로라도 시도하지 않도록 한다.

 

2. SQL Error [934] [42000]: ORA-00934: group function is not allowed here

SELECT avg(basicpay) FROM tblinsa; --15556526

평균 급여보다 더 많이 받는 직원을 구한다고 가정을 해보자.

먼저 평균 급여를 계산하였다.

 

SELECT * FROM tblinsa WHERE basicpay >= 1556526;

그리고 이 평균 값을 상수로 이용해 평균 급여보다 더 많이 받는 직원을 출력하였다.

이때 급여는 계속 바뀔 수 있으므로 상수로 급여를 비교하는 건 좋지 않다는 생각이 들 수 있다.

 

where절에는 집계 함수를 사용할 수 없다.

SELECT * FROM tblinsa WHERE basicpay >= avg(basicpay);

그래서 평균 급여 계산한 식을 비교하면 되지 않을까? 싶지만 에러가 발생한다.😖

에러 내용을 살펴보니 집계 함수는 여기에 작성할 수 없다는 오류 메시지이다. where절에는 집계 함수를 사용할 수 없기 때문에 에러가 발생한다.

where절은 앞의 레코드를 한 개씩 접근해서 조건을 검증한다.
집계 함수는 집합이고, 컬럼은 개인 값이다. Where절에서 집합 데이터와 개인 데이터를 동시에 표현하는 것이 불가능하기 때문에 불가능한 것이다.
결론적으로 where절은 레코드(개인)의 데이터를 접근해서 조건을 검색하므로 집합값을 호출하는 게 불가능하다.
이를 우회해서 억지로 되게 하는 방법이 있으나, 기본적인 구문 상에서는 불가능하다.

 

💡집계 함수의 종류

---

count()

number count(컬럼명)

count 함수는 결과 테이블의 레코드 수를 반환한다. (인자값 컬럼의 숫자를 카운트)

count 함수는 인자값도 있고, 반환값도 있는 함수이다.

 

count 함수의 사용

SELECT count(*) FROM tblCountry; --14
SELECT count(name) FROM tblCountry; --14
SELECT count(population) FROM tblCountry; --13

위 3개의 쿼리는 같은 테이블의 컬럼을 출력하므로 결과도 같을 것으로 보이지만, 그렇지 않다.
count 함수는 단순히 개수를 세는 게 아니므로 인자로 어떤 컬럼이 들어가느냐에 따라서 결과에 차이가 있다.

위의 표에서 레코드는 14개이지만, 값을 가지고 있는 레코드는 13개이다.

count 함수는 실제로 눈에 보이는 숫자를 센다. 그래서 count 함수로 나온 결과에 차이가 있는 것이다.

이처럼 count는 null을 제외한다는 특징이 있다.

 

연락처가 없는 직원 수 계산

-- 모든 직원 수
SELECT count(*) FROM tblinsa; --60

-- 연락처가 있는 직원 수
SELECT count(tel) FROM tblinsa; --57

-- 연락처가 없는 직원 수
SELECT count(*) - count(tel) FROM tblinsa; --3

-- 다른 방법
SELECT count(*) FROM tblinsa WHERE tel IS NULL; --3

count 함수를 이용해 모든 직원 중에서 연락처가 없는 직원이 몇 명인지 계산할 수 있다.

 

부서의 총 개수 계산

SELECT count(DISTINCT buseo) FROM tblinsa; --7

count 함수에 DISTINCT 키워드를 이용하여 중복값을 제거하여 부서의 총개수를 계산할 수 있다.

 

성별별 인원 수 계산

SELECT count(*) FROM tblcomedian WHERE gender = 'm'; --8
SELECT count(*) FROM tblcomedian WHERE gender = 'f'; --2

 

성별별 인원 수 테이블로 나타내기

SELECT
	count(CASE
		WHEN gender = 'm' THEN 1
	END) AS 남자인원수,
	count(CASE
		WHEN gender = 'f' THEN 1
	END) AS 여자인원수
FROM TBLCOMEDIAN;

위 쿼리의 경우 공식처럼 많이 사용되므로 기억해 두는 게 좋다.

case end 구문과 count 함수를 함께 사용하는 방법이다,

 

부서별 인원 수 나타내기

SELECT count(*) FROM tblinsa WHERE buseo = '기획부'; --7
SELECT count(*) FROM tblinsa WHERE buseo = '총무부'; --7
SELECT count(*) FROM tblinsa WHERE buseo = '개발부'; --14

SELECT 
	count(CASE 
		WHEN buseo = '기획부' THEN 'O'		
	END) AS 기획부,
	count(CASE
		WHEN buseo = '총무부' THEN 'O'
	END) AS 총무부,
	count(CASE
		WHEN buseo = '개발부' THEN 'O'
	END) AS 개발부,
	count(*) AS 전체인원수,
	count(
		CASE
			WHEN buseo NOT IN ('기획부', '총무부', '개발부') THEN 'O'
		END
	) AS 나머지
FROM tblinsa;

위 공식을 활용하면 이렇게 응용할 수도 있다.

 

sum()

number sum(컬렴명)

sum 함수는 해당 컬럼의 합을 구한다.

인자의 컬럼은 숫자형만 가능하다. 문자형이나 다른 자료형을 넣으면 에러가 발생한다.

 

SELECT sum(height), sum(weight) FROM tblcomedian;

SELECT sum(FIRST) FROM tblcomedian;

SQL Error [1722] [42000]: ORA-01722: invalid number

숫자형이 아닌 컬럼을 인자로 넣자 유효하지 않다는 에러가 발생했다.

 

sum 함수의 사용

SELECT 
	sum(basicpay) AS "지출 급여 합",
	sum(sudang) AS "지출 수당 합",
	sum(basicpay) + sum(sudang) AS "총 지출",
	sum(basicpay + sudang) AS "총 지출"
FROM tblinsa;

 

avg()

number avg(컬럼명)

avg 함수는 해당 컬럼의 평균값을 구한다.
sum 함수와 마찬가지로 숫자형의 컬럼만 받는다.

 

avg 함수의 사용

SELECT sum(basicpay) / 60 FROM tblinsa; --1556526
SELECT sum(basicpay) / count(*) FROM tblinsa; --1556526
SELECT avg(basicpay) FROM TBLINSA; --1556526

sum 함수와 count 함수를 이용해 평균을 구하는 방법도 있지만, avg 함수를 이용하면 동일한 결과를 간단하게 출력할 수 있다.

 

avg 함수 사용시 주의할 점

SELECT avg(population) FROM tblcountry; --15588
SELECT sum(population) / count(*) FROM tblcountry; --14475
SELECT sum(population) / count(population) FROM tblcountry; --15588

SELECT count(population), count(*) FROM tblcountry;

두 SELECT문의 실행 결과가 동일할 듯 하지만 실제로는 다르다. 이 문제는 count 함수가 null 값을 카운트하지 않는 것에서 발생한다.
null값이 평균에 영향을 주지 않기 때문에 둘 다 맞는 평균이긴 하지만, 참여하지도 않은 국가를 평균에 계산하는 건 맞지 않기 때문에 수학적으로는 15588이 맞는 표현(null을 제외한 13으로 나눈 평균값)이다.

 

1. 균등 지급: 총 지급액 / 모든 직원수 = sum() / count(*)
2. 차등 지급: 총 지급액 / 1팀 직원수 = sum() / count(1팀)

회사에서 성과급을 지급할 때 1팀으로 인해 실적이 좋게 나왔다고 할 때, 직원들에게 균등하게 지급할지, 실적을 낸 1팀에게만 지급을 할지에 따라 다른 결과가 나오듯, 상황에 맞게 적용하면 된다.

 

max() / min()

object max(컬럼명)
object min(컬럼명)

max 함수는 최댓값을 반환하고, min 함수는 최솟값을 반환한다.

SQL에는 object라는 표현이 없지만, 숫자 이외의 값을 반환하기도 하므로 object라는 표현을 사용하였다.

 

max, min 함수의 사용

SELECT max(sudang), min(sudang) FROM tblInsa;

숫자형

 

SELECT max(name) FROM TBLINSA;

문자형

 

SELECT max(ibsadate), min(ibsadate) FROM TBLINSA;

날짜형

 

SELECT
	count(*) AS 직원수,
	sum(basicpay) AS 총급여합,
	avg(basicpay) AS 평균급여,
	max(basicpay) AS 최고급여,
	min(basicpay) AS 최저급여
FROM tblinsa;