[Oracle DB] GROUP BY와 DISTINCT, HAVING 절

DISTINCT

컬럼에 중복 내용이 있으면 대표값 1개만 출력

- 형식
distinct 컬럼명

- 적용
-- sungjuk 테이블 생성
create table sungjuk (
   sno   int          primary key                  
  ,uname varchar(50)  not null
  ,kor   int          check(kor between 0 and 100) 
  ,eng   int          check(eng between 0 and 100) 
  ,mat   int          check(mat between 0 and 100) 
  ,addr  varchar(50)  check(addr in ('Seoul','Jeju','Busan','Suwon'))
  ,tot   int          default 0
  ,aver  int          default 0
  ,wdate date         default sysdate              
);


select addr from sungjuk;   
select addr from sungjuk order by addr; --asc 생략가능
select addr from sungjuk order by addr asc; 
select addr from sungjuk order by addr desc;

select distinct (addr) from sungjuk;   
select distinct (uname) from sungjuk;

 

GROUP BY

컬럼에 동일 내용끼리 그룹화 시킴

- 형식 
group by 칼럼1, 칼럼2, 칼럼3

-- 주소가 동일한 값을 그룹화시키고 주소를 조회 
select addr   
from sungjuk
group by addr; --distinct와 결과값 동일

-- ORA-00979: GROUP BY 표현식이 아닙니다. - "not a GROUP BY expression"
select addr, uname -- 그룹시키고 나올 수 있는 값은 1개만 가능한 값만 조회
from sungjuk
group by addr; --에러

 

문제

--문1) 주소별 인원수를 조회하시오
select addr, count(*)
from sungjuk
group by addr;

select addr, count(*) as cnt --칼럼명 임시 부여
from sungjuk
group by addr;

select addr, count(*) cnt --as 생략가능
from sungjuk
group by addr;

--주소별 오름차순 정렬해서 조회
select addr
from sungjuk
group by addr
order by addr; --asc 생략가능

--주소별 내림차순 정렬해서 조회
select addr
from sungjuk
group by addr
order by addr desc;

--주소별 인원수를 내림차순 정렬해서 조회
select addr, count(*) 
from sungjuk
group by addr
order by count(*) desc;

select addr, count(*) as cnt --해석순서 3)
from sungjuk                 --해석순서 1)
group by addr                --해석순서 2)
order by cnt desc;           --해석순서 4)

 

집계함수

데이터를 그룹별로 나누면 그룹별로 집계 데이터를 도출하는 것이 가능

COUNT(*)	전체 행(Row)을 Count하여 반환
COUNT(컬럼)	컬럼값이 Null인 행을 제외하고 Count하여 반환
COUNT(DISTINCT 컬럼)	컬럼값이 Null이 아닌 행에서 중복을 제거한 Count를 반환
SUM(컬럼)	컬럼값들의 합계를 반환
AVG(컬럼)	컬럼값들의 평균을 반환
MIN(컬럼)	컬럼값들의 최소값을 반환
MAX(컬럼)	컬럼값들의 최대값을 반환

--문2) 주소별 국어점수에 대해서 집계하시오
select addr, count(*), max(kor), min(kor), sum(kor), avg(kor) --갯수, 최대값, 최소값, 합계, 평균
from sungjuk
group by addr;

--주소순으로 정렬
select addr, count(*), max(kor), min(kor), sum(kor), avg(kor) --갯수, 최대값, 최소값, 합계, 평균
from sungjuk
group by addr
order by addr;

--round(값, 0) 소수점 이하값에서 반올림하고 소수점은 없음
select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor),0)
from sungjuk
group by addr
order by addr;

--국어평균을 소수점 없이 반올림하고 내림차순 정렬해서 조회
select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor),0)
from sungjuk
group by addr
order by round(avg(kor),0) desc;

--칼럼명 부여
select addr
        , count(*) as cnt
        , max(kor) as max_kor
        , min(kor) as min_kor
        , sum(kor) as sum_kor
        , round(avg(kor),0) as avg_kor
from sungjuk
group by addr
order by round(avg(kor),0) desc;

--문3) 총점(tot), 평균(aver) 구하시오
update sungjuk set tot=kor+eng+mat, aver=(kor+eng+mat)/3;
commit;
select * from sungjuk;

--문4) 평균(aver)이 80점이상 행을 대상으로 주소별 인원수를 인원수순으로 조회하시오

select * from sungjuk order by aver desc, addr;

select addr
from sungjuk
where aver>=80
group by addr;

select addr,count(*)
from sungjuk
where aver>=80
group by addr;

select addr,count(*)    --4)
from sungjuk            --1)
where aver>=80          --2)
group by addr           --3)
order by count(*);      --5)

 

ORDER BY 

SELECT 문에서 논리적으로 맨 마지막에 수행

 

·SELECT 문의 논리적 수행 순서

  - SELECT ⑤

  - FROM ①

  - WHERE ②

  - GROUP BY ③

  - HAVING ④

  - ORDER BY ⑥

 

- ASC(Ascending) : 오름차순 → 옵션 생략 시 ASC가 기본값

- DESC(Descending) : 내림차순

--주소별 순으로 조회하시오
select uname, addr, kor, eng, mat
from sungjuk
order by addr, kor;

--주소별(1차) 그룹을 하고, 주소가 같다면 국어점수(2차)로 그룹화하기
select addr, kor
from sungjuk 
group by addr, kor
order by addr;

select addr, kor, count(*)
from sungjuk 
group by addr, kor
order by addr;

--1차그룹(kor), 2차그룹(eng), 3차그룹(mat)
select kor, eng, mat, count(*)
from sungjuk
group by kor, eng, mat
order by kor, eng, mat;

 

HAVING 

GROUP BY 절을 사용할 때 WHERE 절처럼 사용하는 조건절

주로 데이터를 그룹핑한 후 특정 그룹을 골라낼 때 사용

- 형식
having 조건절

- 적용
-- 주소별 인원수를 조회하시오
select addr, count(*)
from sungjuk
group by addr;

-- 주소별 인원수가 3인 행을 조회하시오
select addr, count(*)
from sungjuk
group by addr
having count(*)=3;

-- 주소별 인원수가 3이상인 행을 조회하시오
select addr, count(*)
from sungjuk
group by addr
having count(*)>=3;

--문1)주소별 국어 평균값이 50이상 행을 조회하시오
--   (단, 평균값은 소수점없이 반올림)
1) 주소별 국어 평균값 구하기
select addr, avg(kor)
from sungjuk
group by addr;

2) 국어 평균값을 소수점에서 반올림하기
select addr, round(avg(kor), 0)
from sungjuk
group by addr;

3) 2)결과에서 국어 평균값이 50이상 행 조회
select addr, round(avg(kor), 0)
from sungjuk
group by addr
having round(avg(kor),0)>=50;

4) 3)결과에서 국어평균값을 내림차순 조회
select addr, round(avg(kor), 0)
from sungjuk
group by addr
having round(avg(kor),0)>=50
order by round(avg(kor), 0) desc;

5) 4)결과에서 국어평균값 칼럼명을 avg_kor로 변경하기
select addr, round(avg(kor), 0) as avg_kor
from sungjuk
group by addr
having round(avg(kor),0)>=50
order by round(avg(kor), 0) desc;

--문2) 평균(aver)이 70이상 행을 대상으로 주소별 인원수를 구한후
--     그 인원수가 2미만 행을 인원수 순으로 조회하시오
1) 전체 행 조회
select * from sungjuk order by aver desc; 

2) 평균(aver)값이 70이상 행 조회
select * 
from sungjuk 
where aver>=70;

3) 2)결과에서 주소별 인원수를 구하시오
select addr, count(*)
from sungjuk 
where aver>=70
group by addr;

4) 3)결과에서 그 인원수가 2미만 행 조회
select addr, count(*)
from sungjuk 
where aver>=70
group by addr
having count(*)<2;

5) 4)결과에서 인원수 순으로 조회
select addr, count(*) as cnt
from sungjuk 
where aver>=70
group by addr
having count(*)<2
order by cnt;  -- order by count(*) 써도 됨