DATABASE STEP 17 - Group By

★ Group By

• 레코드를 대상으로 그룹을 나누는 역할
• 특정 컬럼을 대상으로 같은 값을 가지는 레코드들끼리 그룹을 묶는 역할
• 그룹을 왜 나누는지? > 각각의 나눠진 그룹을 대상 > 집계 함수를 적용하기 위해서
• 실행 순서 및 구조

select 컬럼리스트  -- 4. 컬럼을 선택
from 테이블         -- 1. 테이블로부터
where 조건          -- 2. 원하는 레코드를
group by 기준      -- 3. 그룹을 나눠서
order by 정렬조건 -- 5. 정렬한다

 

■ 사용 ex)

• tblIsan 테이블의 부서별 평균 급여

select * from tblInsa;

select avg(basicpay) from tblInsa; -- 155만원, 60명

select distinct buseo from tblInsa; -- 7개

select round(avg(basicpay)) from tblInsa where buseo = '총무부'; --171
select round(avg(basicpay)) from tblInsa where buseo = '개발부'; --138
select round(avg(basicpay)) from tblInsa where buseo = '영업부'; --160
select round(avg(basicpay)) from tblInsa where buseo = '기획부'; --185
select round(avg(basicpay)) from tblInsa where buseo = '인사부'; --153
select round(avg(basicpay)) from tblInsa where buseo = '자재부'; --141
select round(avg(basicpay)) from tblInsa where buseo = '홍보부'; --145

select 
    round(avg(basicpay)),
    buseo
from tblInsa
    group by buseo;

• 남자 몇명? 여자 몇명? > 남녀별 각각 몇명인지?

select
    count(*),
    gender
from tblComedian
    group by gender;

• 대륙별 국가수?

select 
    count(*),
    continent
from tblCountry
    group by continent;

• 부서별 부서 ex)

select 
    buseo,
    count(*) as "부서별 인원수",
    sum(basicpay) as "부서별 급여 합",
    round(avg(basicpay)) as "부서별 평균 급여",
    max(ibsadate) as "부서별 막내의 입사일",
    min(ibsadate) as "부서별 고참의 입사일"
from tblInsa
    group by buseo;

• 남녀별 ex)

select 
    gender,
    round(avg(height)) as "남녀별 평균 키",
    round(avg(weight), 1) as "남녀별 평균 몸무계",
    max(height) as "키가 가장 큰사람",
    min(height) as "키가 가장 작은사람"
from tblComedian
    group by gender;

• 직업별 인원수?

select 
    job,
    count(*)
from tblAddressBook
    group by job
        --order by count(*) desc;
        order by job asc;

• 다중 그룹 (1차 그룹[부서] > 2차 그룹[직위])

select 
    buseo as "부서명",
    jikwi as "직위명",
    count(*) as "인원수"
from tblInsa
    group by buseo, jikwi
        order by buseo, jikwi;

• 성별 인원수

select  
    count(*),
    substr(ssn, 8, 1) as "성별"
from tblInsa
    group by substr(ssn, 8, 1);

• 지역별 인원수

select 
    substr(address, 1, instr(address, ' ') -1), -- -- 공백을 기준으로 공백만큼 짤라옴
    count(*)
from tblAddressBook
    group by substr(address, 1, instr(address, ' ') -1);

• 이메일 사이트별 인원수

select
    substr(email, instr(email, '@')+1) as "사이트",
    count(*) as "회원 수"
from tblAddressBook
    group by  substr(email, instr(email, '@')+1)
        order by count(*) desc;

• 급여별 그룹 > 인원수? ( 100만원 이하, 100~200만원, 200만원 이상)

select
    basicpay,
    floor(basicpay / 1000000)
from tblInsa;
     
select
   (floor(basicpay / 1000000) +1) * 100 || '만원 이하' as "급여대",
    count(*) as "인원수"
from tblInsa
    group by floor(basicpay/1000000)
        order by floor(basicpay / 1000000) asc;

• 한일? 안한일? 각각 몇개?

select 
    completedate,
    count(*)
from tblTodo
    group by completedate;

select
    count(*) as "개수",
     case
        when completedate is null then '안한일'
        when completedate is not null then '한일'
    end as "완료/미완료"
from tblTodo
    group by  case
            when completedate is null then '안한일'
            when completedate is not null then '한일'
                end;

 

■ Group By 요구사항

-- tblZoo. 종류(family)별 평균 다리의 갯수를 가져오시오.
select *
from tblzoo;
select 
    family as "종류",
    round(avg(leg),1)
from tblzoo
    group by Family;
    
-- tblZoo. 사이즈와 종류별로 그룹을 나누고 각 그룹의 갯수를 가져오시오.
select 
    count(*) as "개수",
    sizeof as "크기", family as "어종"
from tblzoo
    group by sizeof, family
        order By family asc, sizeof desc;
        
-- tblAddressBook. 이메일이 스네이크 명명법으로 만들어진 사람들 중에서 여자이며, 20대이며, 키가 150~160cm 사이며, 고향이 서울 또는 인천인 사람들만 가져오시오.
select *
from tblAddressBook
    where instr(email,'_') <>0 and 
        Gender = 'f' and 
        height between 150 and 160 
        and hometown in ('서울', '인천');