게시일: Oct 22, 2011 3:10:33 AM
- COUNT : 건수 조회시 사용
select count(*) cnt_1 -- * 은 null 값을 포함한 모든 건수
, count(code) cnt_2 -- 컬럼이 오는 경우는 null 제외
, count(all code) cnt_3 -- 위와 동일
, count(distinct code) cnt_4 -- null제외, 중복 건수 제외
, count(distinct *) cnt_5 -- ORA-00936: missing expression
from (
select '1' code from dual union all
select '' code from dual union all -- null
select '3' code from dual union all -- 3 중복
select '' code from dual union all -- null
select '*' code from dual union all -- 3 중복
select '3' code from dual union all
select '7' code from dual
)
;
-----------------------------
CNT_1 CNT_2 CNT_3 CNT_4
-----------------------------
7 5 5 4
-----------------------------
- SUM : 합계를 계산한 결과 반환
- MAX : 최대값, MIN : 최소값
- AVG : 평균값
select sum(pocket_money) sum_1
, sum(distinct pocket_money) sum_2 -- 중복건은 한 건만 처리
, max(pocket_money) max_1
, min(pocket_money) min_1
, avg(pocket_money) avg_1
, avg(distinct pocket_money) avg_2
from (
select 200 pocket_money from dual union all
select null pocket_money from dual union all -- null은 계산 대상이 아님
select 300 pocket_money from dual union all
select null pocket_money from dual union all -- null은 계산 대상이 아님
select 100 pocket_money from dual union all -- 중복
select 100 pocket_money from dual union all -- 중복
select 900 pocket_money from dual
)
;
---------------------------------------------
SUM_1 SUM_2 MAX_1 MIN_1 AVG_1 AVG_2
---------------------------------------------
1600 1500 900 100 320 375
---------------------------------------------
select sum(pocket_money) sum_1
from (
select 200 pocket_money from dual union all -- number
select '200' pocket_money from dual -- varchar
)
;
-- ORA-01790: expression must have same datatype as corresponding expression
- STDDEV : 표준편차, VARIANCE : 분산도
select stddev(pocket_money) stddev_1
, stddev(distinct pocket_money) stddev_2
, variance(pocket_money) variance_1
, variance(distinct pocket_money) variance_2
from (
select 200 pocket_money from dual union all
select null pocket_money from dual union all
select 300 pocket_money from dual union all
select null pocket_money from dual union all
select 100 pocket_money from dual union all
select 100 pocket_money from dual union all
select 900 pocket_money from dual
)
;
---------------------------------------------------------------------------------------------------------------------------------------------
STDDEV_1 STDDEV_2 VARIANCE_1 VARIANCE_2
---------------------------------------------------------------------------------------------------------------------------------------------
334.664010613630219191268810314074995757 359.397644214130411245565696650019745961 112000 129166.6666666666666666666666666666666667
---------------------------------------------------------------------------------------------------------------------------------------------