그룹함수

select 

     deptno as '부서번호',
     case when substring(jubun,7,1) in ('1','3') then '남'
     else '여'
     end as '성별',

     count(*) as '인원수'
from sawon
  group by deptno, case when substring(jubun,7,1) in ('1','3') then '남'
    else '여'end

 

select 
     deptno as '부서번호',
     case when substring(jubun,7,1) in ('1','3') then '남'
     else '여'
     end as '성별',

     count(*) as '인원수'
from sawon
  group by deptno, case when substring(jubun,7,1) in ('1','3') then '남'
    else '여'end

with rollup; --  각부서별 총인원수와 모든부서 인원수를 나타내준다.

 

select 
     deptno as '부서번호',
     case when substring(jubun,7,1) in ('1','3') then '남'
     else '여'
     end as '성별',

     count(*) as '인원수'
from sawon
  group by deptno, case when substring(jubun,7,1) in ('1','3') then '남'
    else '여'end

with cube; - rollup에 모든부서 인원수에서 남,여 인원수도 나타내준다 즉 rollup < cube

 

select case grouping(deptno) when 1 then '모든부서' --0,1 값이 나오는데 1은 그룹핑 된거
                else deptno end '부서번호',                     -- 즉 1은 null값 
     case grouping(sex) when 1 then '남녀모두'
  else sex end '성별', count(*)
from (select isnull(convert(char,deptno),'인턴') 'deptno',  -- inner view
  case when substring(jubun,7,1) in ('1','3') then '남'
  else '여'
  end as 'sex'
 from sawon) t
group by deptno,sex
with rollup;

 

select case grouping(deptno) when 1 then '든부서'
  else '모든부서' end '부서번호',
     case grouping(sex) when 1 then '남녀모두'
  else sex end '성별', count(*)
from (select isnull(convert(char,deptno),'인턴') 'deptno',
  case when substring(jubun,7,1) in ('1','3') then '남'
  else '여'
  end as 'sex'
 from sawon) t
group by deptno,sex
with cube;

select name,jik,sal
from sawon
order by jik
compute avg(sal) by jik   --- 직급을 order해뒀기 때문에

 직급별로 avg를 계산한다.