집계함수
SELECT SUM(salary), COUNT(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees;
SUM(SALARY) COUNT(SALARY) AVG(SALARY) MAX(SALARY) MIN(SALARY)
----------- ------------- ----------- ----------- -----------
175500 20 8775 24000 2500
집계함수의 NULL값 처리.
SELECT SUM(commission_pct) AS SUM, COUNT(commission_pct), COUNT(*), AVG(commission_pct) AS AVG, MAX(commission_pct) AS MAX, MIN(commission_pct) AS MIN FROM employees;
SUM COUNT(COMMISSION_PCT) COUNT(*) AVG MAX MIN ---- --------------------- -------- ------ --- ---- 0.85 4 20 0.2125 0.3 0.15
SUM, AVG, MAX, MIN은 무조건 NULL값을 제외하고 계산.
COUNT(COMMISSION_PCT)도 NULL값 제외.
COUNT(*) 의 경우는 rowid를 참조하기때문에 집계됨.
rowid 가상의 컬럼. 실제 레코드가 위치한 물리적인 위치값
DESC employees;
Name Null? Type ----------------------------------------- -------- --------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SELECT rowid FROM employees;
ROWID ------------------------- AAAE8GAABAAALEhAAD AAAE8GAABAAALEhAAE AAAE8GAABAAALEhAAq AAAE8GAABAAALEhAAH AAAE8GAABAAALEhABK AAAE8GAABAAALEhAAx AAAE8GAABAAALEhABM AAAE8GAABAAALEiAAC AAAE8GAABAAALEhABO AAAE8GAABAAALEhAAY AAAE8GAABAAALEhAAC AAAE8GAABAAALEiAAD AAAE8GAABAAALEhAAB
문제1 이 회사의 전체사원의 평균커미션을 출력
SELECT AVG(NVL(commission_pct, 0)), AVG(commission_pct) FROM employees;
AVG(NVL(COMMISSION_PCT,0)) AVG(COMMISSION_PCT)
-------------------------- -------------------
0.0425 0.2125
문제2 salary가 10000이상인 사원의 수
SELECT COUNT(*) FROM employees WHERE salary >= 10000;
문제3 최초입사자는 마지막 입사한 사원보다 며칠을 더 일했는가?
SELECT MAX(hire_date), MIN(hire_date), MAX(hire_date)-MIN(hire_date) FROM employees;
MAX(HIRE_DATE) MIN(HIRE_DATE) MAX(HIRE_DATE)-MIN(HIRE_DATE) --------------------- --------------------- ----------------------------- 2000-01-29 00:00:00.0 1987-06-17 00:00:00.0 4609
문제4 최고급여자와 최저 급여자의 차는?
SELECT MAX(salary), MIN(salary), MAX(salary)-MIN(salary) FROM employees;
문제5 사원들의 업무의 종류는 몇가지?
중복안되게 : UNIQUE, DISTINCT
SELECT UNIQUE job_id FROM employees; SELECT DISTINCT job_id FROM employees;
둘다 같은 결과 JOB_ID ---------- AC_ACCOUNT AC_MGR AD_ASST AD_PRES AD_VP IT_PROG MK_MAN MK_REP SA_MAN SA_REP ST_CLERK ST_MAN
SELECT COUNT(DISTINCT job_id) FROM employees;
COUNT(DISTINCTJOB_ID)
---------------------
12
부서별 급여평균을 구해볼까.
SELECT department_id, AVG(salary) FROM employees;
ORA-00937: not a single-group group function
SELECT department_id, ROUND(AVG(salary),2) FROM employees GROUP BY department_id;
DEPARTMENT_ID ROUND(AVG(SALARY),2)
------------- --------------------
NULL 7000
90 19333.33
20 9500
110 10150
50 3500
80 10033.33
60 6400
10 4400
부서별, 업무별 급여평균
SELECT department_id, job_id, ROUND(AVG(salary),2) FROM employees GROUP BY department_id, job_id ORDER BY department_id, job_id;
DEPARTMENT_ID JOB_ID ROUND(AVG(SALARY),2)
------------- ---------- --------------------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
50 ST_CLERK 2925
50 ST_MAN 5800
60 IT_PROG 6400
80 SA_MAN 10500
80 SA_REP 9800
90 AD_PRES 24000
90 AD_VP 17000
110 AC_ACCOUNT 8300
110 AC_MGR 12000
NULL SA_REP 7000
부서별 급여평균이 8000넘는 부서..
SELECT department_id, avg(salary) FROM employees WHERE AVG(salary) >= 8000 GROUP BY department_id;
ORA-00934: group function is not allowed here
SELECT department_id, ROUND(AVG(SALARY),2) FROM employees GROUP BY department_id; HAVING AVG(salary) >= 8000;
DEPARTMENT_ID ROUND(AVG(SALARY),2)
------------- --------------------
NULL 7000
90 19333.33
20 9500
110 10150
50 3500
80 10033.33
60 6400
10 4400
사원테이블에서 REP문자열을 포함한 업무별 급여 합계중에 13000 달러를
초과하는 업무 그룹을 급여합계의 오름차순으로 출력
SELECT job_id, SUM(salary) AS ss FROM employees WHERE job_id LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY ss;
JOB_ID SS ------ ----- SA_REP 26600
oracle 파싱순서..
4. select
1. from
2. where
3. group by
5. having
6. order by