group by 는 그룹에 대한 부분합계를 볼수 있다. 하지만 상세내역을 볼수는 없다. 상세내역과 그룹합계를 모두 한꺼번에 볼수는 없을까..
그게 바로 compute와 compute by 문이다.
전체 평균과 그룹별 평균을 구해보자
select avg(price) from titles : 전체평균
select type, avg(price)
from titles
group by type : 그룹별 평균
결과
type
———— ———————
business 8.9825
mod_cook 11.4900
popular_comp 21.4750
psychology 13.5040
trad_cook 15.9633
UNDECIDED NULL
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
compute를 쓰면
select type, title_id, price
from titles
order by type
compute avg(price) by type
결과값
type title_id price
———— ——– ———————
business BU1032 1.0000
business BU1111 11.9500
business BU2075 2.9900
business BU7832 19.9900
avg
=====================
8.9825
type title_id price
———— ——– ———————
mod_cook MC2222 19.9900
mod_cook MC3021 2.9900
avg
=====================
11.4900
전체 평균은 compute만 쓴다
select type, title_id, price
from titles
compute avg(price)
결과값
type title_id price
———— ——– ———————
business BU1032 1.0000
business BU1111 11.9500
business BU2075 2.9900
business BU7832 19.9900
mod_cook MC2222 19.9900
mod_cook MC3021 2.9900
UNDECIDED MC3026 NULL
popular_comp PC1035 22.9500
popular_comp PC8888 20.0000
popular_comp PC9999 NULL
psychology PS1372 21.5900
psychology PS2091 10.9500
psychology PS2106 7.0000
psychology PS3333 19.9900
psychology PS7777 7.9900
trad_cook TC3218 20.9500
trad_cook TC4203 11.9500
trad_cook TC7777 14.9900
avg
=====================
13.5793