집계
scott계정으로 접속
년도별 입사자수 출력
SELECT DISTINCT TO_CHAR(hiredate, 'YYYY') FROM emp;
TO_CHAR(HIREDATE,'YYYY') ------------------------ 1980 1982 1981
SELECT hiredate, TO_CHAR(hiredate, 'YYYY') as "1980", TO_CHAR(hiredate, 'YYYY') as "1981", TO_CHAR(hiredate, 'YYYY') as "1982" FROM emp;
HIREDATE 1980 1981 1982 --------------------- ---- ---- ---- 1980-12-17 00:00:00.0 1980 1980 1980 1981-02-20 00:00:00.0 1981 1981 1981 1981-02-22 00:00:00.0 1981 1981 1981 1981-04-02 00:00:00.0 1981 1981 1981 1981-09-28 00:00:00.0 1981 1981 1981 1981-05-01 00:00:00.0 1981 1981 1981 1981-06-09 00:00:00.0 1981 1981 1981 1981-11-17 00:00:00.0 1981 1981 1981 1981-09-08 00:00:00.0 1981 1981 1981 1981-12-03 00:00:00.0 1981 1981 1981 1981-12-03 00:00:00.0 1981 1981 1981 1982-01-23 00:00:00.0 1982 1982 1982
SELECT hiredate, DECODE(TO_CHAR(hiredate, 'YYYY'), 1980, 1, 0) as "1980", DECODE(TO_CHAR(hiredate, 'YYYY'), 1981, 1, 0) as "1981", DECODE(TO_CHAR(hiredate, 'YYYY'), 1982, 1, 0) as "1982" FROM emp;
HIREDATE 1980 1981 1982 --------------------- ---- ---- ---- 1980-12-17 00:00:00.0 1 0 0 1981-02-20 00:00:00.0 0 1 0 1981-02-22 00:00:00.0 0 1 0 1981-04-02 00:00:00.0 0 1 0 1981-09-28 00:00:00.0 0 1 0 1981-05-01 00:00:00.0 0 1 0 1981-06-09 00:00:00.0 0 1 0 1981-11-17 00:00:00.0 0 1 0 1981-09-08 00:00:00.0 0 1 0 1981-12-03 00:00:00.0 0 1 0 1981-12-03 00:00:00.0 0 1 0 1982-01-23 00:00:00.0 0 0 1
SELECT SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1980, 1, 0)) as "1980", SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1981, 1, 0)) as "1981", SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1982, 1, 0)) as "1982" FROM emp;
1980 1981 1982
---- ---- ----
1 10 1
SELECT TO_CHAR(hiredate, 'YYYY') AS HIREDATE, COUNT(*) FROM emp GROUP BY TO_CHAR(hiredate, 'YYYY') ORDER BY 1;
HIREDATE COUNT(*) -------- -------- 1980 1 1981 10 1982 1
oraclejava 계정에서 년도별 입사자 구해..
SELECT DISTINCT TO_CHAR(hire_date, 'YYYY') FROM employees ORDER BY 1;
TO_CHAR(HIRE_DATE,'YYYY') ------------------------- 1987 1989 1990 1991 1993 1994 1995 1996 1997 1998 1999 2000
SELECT SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1987, 1, 0)) AS "1987", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1989, 1, 0)) AS "1989", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1990, 1, 0)) AS "1990", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1991, 1, 0)) AS "1991", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1993, 1, 0)) AS "1993", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1994, 1, 0)) AS "1994", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1995, 1, 0)) AS "1995", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1996, 1, 0)) AS "1996", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1997, 1, 0)) AS "1997", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1998, 1, 0)) AS "1998", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1999, 1, 0)) AS "1999", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2000, 1, 0)) AS "2000" FROM employees;
1987 1989 1990 1991 1993 1994 1995 1996 1997 1998 1999 2000
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
2 1 1 1 1 2 1 2 2 3 3 1
부서별, 업무별 급여통계
scott계정으로 접속
SELECT * FROM dept; SELECT DISTINCT deptno FROM emp;
DEPTNO
------
30
NULL
20
10
SELECT job, DECODE(deptno, 10, sal, 0) AS d10, DECODE(deptno, 20, sal, 0) AS d20, DECODE(deptno, 30, sal, 0) AS d30 FROM emp;
JOB D10 D20 D30 --------- ---- ---- ---- CLERK 0 800 0 SALESMAN 0 0 1600 SALESMAN 0 0 1250 MANAGER 0 2975 0 SALESMAN 0 0 1250 MANAGER 0 0 2850 MANAGER 2450 0 0 PRESIDENT 5000 0 0 SALESMAN 0 0 1500 CLERK 0 0 950 ANALYST 0 3000 0 CLERK 0 0 0
SELECT job, SUM(DECODE(deptno, 10, sal, 0)) AS d10, SUM(DECODE(deptno, 20, sal, 0)) AS d20, SUM(DECODE(deptno, 30, sal, 0)) AS d30 FROM emp GROUP BY job;
JOB D10 D20 D30 --------- ---- ---- ---- CLERK 0 800 950 SALESMAN 0 0 5600 PRESIDENT 5000 0 0 MANAGER 2450 2975 2850 ANALYST 0 3000 0
SELECT job, SUM(DECODE(deptno, NULL, sal, 0)) AS free, SUM(DECODE(deptno, 10, sal, 0)) AS d10, SUM(DECODE(deptno, 20, sal, 0)) AS d20, SUM(DECODE(deptno, 30, sal, 0)) AS d30, SUM(sal) AS total FROM emp GROUP BY job;
JOB FREE D10 D20 D30 TOTAL --------- ---- ---- ---- ---- ----- CLERK 1300 0 800 950 3050 SALESMAN 0 0 0 5600 5600 PRESIDENT 0 5000 0 0 5000 MANAGER 0 2450 2975 2850 8275 ANALYST 0 0 3000 0 3000
oraclejava에서도 해보자.
SELECT * FROM departments; SELECT DISTINCT department_id FROM employees ORDER BY 1;
DEPARTMENT_ID
-------------
10
20
50
60
80
90
110
NULL
SELECT job_id, DECODE(department_id, NULL, salary, 0) AS free, DECODE(department_id, 10, salary, 0) AS d10, DECODE(department_id, 20, salary, 0) AS d20, DECODE(department_id, 50, salary, 0) AS d50, DECODE(department_id, 60, salary, 0) AS d60, DECODE(department_id, 80, salary, 0) AS d80, DECODE(department_id, 90, salary, 0) AS d90, DECODE(department_id, 110, salary, 0) AS d110 FROM employees;
JOB_ID FREE D10 D20 D50 D60 D80 D90 D110 ---------- ---- ---- ----- ---- ---- ----- ----- ----- AD_PRES 0 0 0 0 0 0 24000 0 AD_VP 0 0 0 0 0 0 17000 0 AD_VP 0 0 0 0 0 0 17000 0 IT_PROG 0 0 0 0 9000 0 0 0 IT_PROG 0 0 0 0 6000 0 0 0 IT_PROG 0 0 0 0 4200 0 0 0 ST_MAN 0 0 0 5800 0 0 0 0 ST_CLERK 0 0 0 3500 0 0 0 0 ST_CLERK 0 0 0 3100 0 0 0 0 ST_CLERK 0 0 0 2600 0 0 0 0 ST_CLERK 0 0 0 2500 0 0 0 0 SA_MAN 0 0 0 0 0 10500 0 0 SA_REP 0 0 0 0 0 11000 0 0 SA_REP 0 0 0 0 0 8600 0 0 SA_REP 7000 0 0 0 0 0 0 0 AD_ASST 0 4400 0 0 0 0 0 0 MK_MAN 0 0 13000 0 0 0 0 0 MK_REP 0 0 6000 0 0 0 0 0 AC_MGR 0 0 0 0 0 0 0 12000 AC_ACCOUNT 0 0 0 0 0 0 0 8300
SELECT job_id,
SUM(DECODE(department_id, NULL, salary, 0)) AS free,
SUM(DECODE(department_id, 10, salary, 0)) AS d10,
SUM(DECODE(department_id, 20, salary, 0)) AS d20,
SUM(DECODE(department_id, 50, salary, 0)) AS d50,
SUM(DECODE(department_id, 60, salary, 0)) AS d60,
SUM(DECODE(department_id, 80, salary, 0)) AS d80,
SUM(DECODE(department_id, 90, salary, 0)) AS d90,
SUM(DECODE(department_id, 110, salary, 0)) AS d110,
SUM(salary) AS TOTAL
FROM employees
GROUP BY job_id;
JOB_ID FREE D10 D20 D50 D60 D80 D90 D110 TOTAL ---------- ---- ---- ----- ----- ----- ----- ----- ----- ----- IT_PROG 0 0 0 0 19200 0 0 0 19200 AC_MGR 0 0 0 0 0 0 0 12000 12000 AC_ACCOUNT 0 0 0 0 0 0 0 8300 8300 ST_MAN 0 0 0 5800 0 0 0 0 5800 AD_ASST 0 4400 0 0 0 0 0 0 4400 AD_VP 0 0 0 0 0 0 34000 0 34000 SA_MAN 0 0 0 0 0 10500 0 0 10500 MK_MAN 0 0 13000 0 0 0 0 0 13000 AD_PRES 0 0 0 0 0 0 24000 0 24000 SA_REP 7000 0 0 0 0 19600 0 0 26600 MK_REP 0 0 6000 0 0 0 0 0 6000 ST_CLERK 0 0 0 11700 0 0 0 0 11700
.
.
.
.