집계 함수는 WHERE절
에 사용 불가능
SELECT SUM(필드) FROM테이블;
SELECT SUM(BOOK_ID) FROM BOOK;
SELECT SUM(SALARY) FROM EMPLOYEES;
SELECT AVG(필드 FROM테이블;
SELECT AVG(BOOK_ID) FROM BOOK;
SELECT AVG(SALARY) FROM EMPLOYESS WHERE DEPARTMENT_ID = 50;
SELECT MAX(필드) FROM테이블;
SELECT MAX(BOOK_ID) FROM BOOK;
SELECT MIN(필드) FROM테이블;
SELECT MIN(BOOK_ID) FROM BOOK;
SELECT MIN(HIRE_DATE) FROM EMPLOYEES;
SELECT COUNT(*) FROM 테이블;(입력된레코드개수)
SELECT COUNT(필드) FROM 테이블; (지정한필드에입력된데이터 개수)
-> 집계함수의결과는 값
이다. (검색된 레코드가아니라는의미!)
SELECT COUNT(*) FROM STUDENT_RESULTS WHERE SCORE >= 80;
SELECT COUNT(*) FROM STUDENT_RESULTS WHERE SCORE >= (SELECTAVG(SCORE)FROM STUDENT_RESULTS);
ALL
-> 중복 허용, DISTINCT
-> 중복 비허용. COUNT() 함수에 적용 가능SELECT COUNT(ALL FIRST_NAME) FROM EMPLOYEES;
SELECT COUNT(ALL FIRST_NAME), COUNT(DISTINCT FIRST_NAME) FROM EMPLOYEES;
SELECT ABS(필드)FROM테이블;
SELECT ABS(-23) FROM DUAL;
DUAL
: oracle에서 제공하는 dummy tableSELECT SIGN(필드) FROM테이블;
SELECT SIGN(23), SIGN(-23), SIGN(0) FROM DUAL;
SIGN(23) | SIGN(-23) | SIGN(0) |
---|---|---|
1 | -1 | 0 |
SELECT ROUND(필드) FROM테이블;
SELECT ROUND(필드, 인덱스) FROM테이블;
SELECT ROUND(0.123), ROUND(0.543) FROM DUAL;
ROUND(0.123) | ROUND(2.543) |
---|---|
0 | 3 |
SELECT ROUND(0.12345678, 6), ROUND(2.3423455,4) FROM DUAL;
ROUND(0.12345678, 6) | ROUND(2,3423455, $) |
---|---|
0.123457 | 2.3423 |
SELECT TRUNC(필드) FROM테이블;
SELECT TRUNC(필드, 인덱스) FROM테이블;
SELECT TRUNC(1234.1234567,0) ZERO FROM DUAL;
ZERO |
---|
1234 |
SELECT TRUNC(1234.1234567,2) FROM DUAL;
TRUNC(1234.1234567,2) |
---|
1234.12 |
SELECT TRUNC(1234.1234,-1) FROM DUAL;
TRUNC(1234.1234,-1) |
---|
1230 |
SELECT CEIL(필드) FROM 테이블;
SELECT CEIL(32.8) CEIL FROM DUAL;
SELECTCEIL(32.3) CEIL FROM DUAL;
CEIL |
---|
33 |
SELECT FLOOR(필드) FROM 테이블;
SELECT FLOOR(32.8) FLOOR FROM DUAL;
SELECT FLOOR(32.3) FLOOR FROM DUAL;
FLOOR |
---|
32 |
SELECT POWER(필드, 인덱스) FROM 테이블;
SELECT POWER(4,2) POWER1 FROM DUAL;
POWER1 |
---|
16 |
SELECT MOD(필드, 인덱스) FROM 테이블;
SELECT MOD(7,4) MOD1 FROM DUAL;
MOD1 |
---|
3 |
SELECT SQRT(필드) FROM 테이블;
SELECT SQRT(2), SQRT(3) FROM DUAL;
SQRT(2) | SQRT(3) |
---|---|
1.4142... | 1.7320... |
Reference