집계함수
- 주어진 자료를 특정 컬럼값을 기준으로 그룹화하고 그룹화된 자료들에 대하여 합, 평균, 자료수, 최대값, 최소값을 구하는 함수
- 다중행 함수(결과가 복수개인 함수)
- 중첩사용을 할 수 없음
- SELECT절에 일반 컬럼, 수식, 일반함수(집계함수 제외한 함수) 등과 같이 사용하면 반드시 GROUP BY절을 기술해야함
(사용형식)
SELECT[컬럼 LIST]
FROM 테이블명
[WHERE 조건] -- 일반 조건(날짜, 부서코드 등 조회할 때)
[GROUP BY 컬럼명1[,컬럼명2...]][HAVING 조건] -- 5가지 집계함수에 부여된 조건(합이 500만원보다 많거나 등)
[ORDER BY 컬럼명|컬럼인덱스 [ASC|DESC],...];
. GROUP BY 컬럼명1[,컬럼명2...] - '컬럼명1'을 기준으로(같은 값) 그룹화하고 그룹화된 상태에서 '컬럼명2'로 또다시 소그룹화
. HAVING 조건 - 집계함수에서 조건이 부여된 경우 사용
IN ANY SOME ALL EXIST - 다중행 처리 함수들
SELECT절에 집계함수만 나왔을 때는 GROUP BY절 사용하지 않음.
집계함수 자체에 조건이 부여되었을 때에는 HAVING 절을 사용.
JOIN조건 사용시 100% WHERE절을 사용해야함.
1)SUM(col|expr)
- 주어진 매개변수의 값들의 합을 반환
사용예)사원테이블에서 사원들의 급여 합계를 구하시오. --합계만 출력해야함
SELECT SUM(SALARY),
--COUNT(*) 사원수 구하기
FROM EMP; --EMP테이블이 하나의 그룹이 됨.
사용예)사원테이블에서 부서별 사원들의 급여 합계를 구하시오.
SELECT DEPARTMENT_ID AS 부서코드,
SUM(SALARY) AS 급여합계
FROM HR.EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
WHERE절에는 그룹함수가 허용되지 않는다.
사용예)사원테이블에서 부서별 사원들의 급여합계가 50000이상되는 부서와 합계를 구하시오.
SELECT DEPARTMENT_ID AS 부서코드,
SUM(SALARY) AS 급여합계
FROM HR.EMP
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY)>=50000 --이걸 WHERE절로 사용하면 오류가 남
ORDER BY 1;
사용예)매입테이블에서 1-6월 월별 매입집계를 조회하시오.
Alias는 월,매입수량합계,매입금액합계이다.
SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
SUM(BUY_QTY) AS 매입수량합계,
SUM(BUY_COST*BUY_QTY) AS 매입금액합계
FROM BUYPROD
WHERE EXTRACT(MONTH FROM BUY_DATE) BETWEEN 1 AND 6
GROUP BY EXTRACT(MONTH FROM BUY_DATE) --일별이 아닌 월별이라서 MONTH사용 /
사용된 일반함수는 반드시 GROUP BY절 뒤에 기술되어야 한다.
ORDER BY 1;
사용예)회원테이블에서 연령대별 마일리지 합계를 구하시오
SELECT TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1) AS 연령대,
SUM(MEM_MILEAGE) AS "마일리지 합계"
FROM MEMBER
GROUP BY TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)
ORDER BY 1;사용예)회원테이블에서 성별 마일리지 합계를 구하시오 * CASE WHEN 사용법
CASE WHEN ~ THEN ... END
SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','3') THEN '남자' ELSE '여자' END AS 성별,
SUM(MEM_MILEAGE) AS 마일리지합계
FROM MEMBER
GROUP BY CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','3') THEN '남자' ELSE '여자' END;사용예)장바구니테이블에서 2005년 5월 회원별,상품별 구매집계를 조회하시오 --회원번호 같은사람이 같은 물품을 몇개나 샀는지
Alias는 회원번호,상품명,구매수량,구매금액
SELECT A.CART_MEMBER AS 회원번호,
B.PROD_NAME AS 상품명,
SUM(A.CART_QTY) AS 구매수량,
SUM(A.CART_QTY*B.PROD_PRICE) AS 구매금액
FROM CART A, PROD B --FROM절에 두개의 테이블이 사용되었을 때는 WHERE절이 필수
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '200505%'
GROUP BY ROLLUP (A.CART_MEMBER, B.PROD_NAME)
ORDER BY 4 DESC;
2)AVG(col)
- 'col'에 저장된 값들의 산술 평균을 반환 --산술평균: 합을 자료의 수로 나눈 값
사용예)사원테이블에서 모든 사원들의 평균급여
SELECT ROUND(AVG(SALARY))
FROM HR.EMP;사용예)각 부서별 평균급여를 조회하시오.
SELECT DEPARTMENT_ID AS 부서코드,
ROUND(AVG(SALARY)) AS 평균급여 --ROUND 소수점 이하 자리 반올림
FROM HR.EMP
GROUP BY DEPARTMENT_ID
ORDER BY 1;
사용예)사원테이블에서 평균급여보다 급여가 적은 사원들을 조회하시오.
Alias는 사원번호,사원명,부서코드,급여
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여 --ROUND 소수점 이하 자리 반올림
FROM HR.EMP
WHERE SALARY<=(SELECT AVG(SALARY) --GROUP BY사용하면 안됨.
FROM HR.EMP)
ORDER BY 4 DESC;
사용예)2005년 5월 상품분류별 판매수량합계를 조회하시오 => 테이블 3개끼리 조인
Alias 분류명,판매수량합계
SELECT B.LPROD_NM AS 분류명,
SUM(A.CART_QTY) AS 판매수량합계
FROM CART A, LPROD B, PROD C
WHERE A.CART_PROD = C.PROD_ID
AND C.PROD_LGU=B.LPROD_GU
AND A.CART_NO LIKE '200505%'
GROUP BY B.LPROD_NM;
--사용되는 테이블을 먼저 결정해야함. 테이블이 3개 사용되어야함. 테이블이 3개 사용되면 JOIN조건은 적어도 2개
