SQL 미니 과제와 함께하는 쿼리 공부하기 -2 GROUP BY

2022.10.06 - [개발/SQL] - SQL 미니 과제와 함께하는 쿼리 공부하기 -1 테이블 정의 , Join

         GROUP BY사용하기

인터넷에 grouop by를 검색해서 공부하면 같은 값을 가진 행끼리 하나의 그룹으로 뭉쳐줍니다 라고 정의를 내립니다 

하지만 저의 직관적인 해석을 하자면 그룹바이의 선택된 컬럼의 중복된 행을 숨김처리 하는 기능이라고 생각합니다 

https://kimsyoung.tistory.com/

 

 

요약

-- invc_qty가 5이상인 invc_no출력을 하는데 중복되는 행을 제거한다 
SELECT a.invc_no FROM TB_INVC as a (nolock)
INNER JOIN TB_INVC_DTL as ad (nolock) 
ON a.invc_no=ad.invc_no
INNER JOIN TB_STOCK as s (nolock)
ON ad.prod_cd=s.prod_cd
WHERE ad.invc_qty>=5
GROUP BY a.invc_no

⁂주의사항

- SELECT에서 불러오는 컬럼과 GROUPBY 로 묶어주는 컬럼이 매칭이 되어야한다

 SELECT 문에 있는 모든 열은 집계 함수가 되거나 GROUP BY 절에 나타나야 합니다.  SELECT 문에 집계 함수를 사용하지 않거나 GROUP BY 절에 언급되지 않은 열이 존재한다면 오류가 발생합니다

- 인보이스의 총수량 (invc_qtot: 인보이스마다 총수량이기에 invoice 한개당 한개의 값을 매치) 를 위 데이터에 합치고 싶다면 두가지 방법이 있다.

• GROUPBY 로 묶어주기
• MAX로 묶기

SELECT a.invc_no, ad.prod_cd,MAX(ad.invc_qty) AS inv_qty
FROM TB_INVC as a (nolock)
INNER JOIN TB_INVC_DTL as ad (nolock) ON a.invc_no=ad.invc_no
INNER JOIN TB_STOCK as s (nolock) ON ad.prod_cd=s.prod_cd
where a.invc_no ='IN00041653'
and ad.invc_qty >= 5
GROUP BY ad.prod_cd, a.invc_no

'IN00041653' 의 상품 수량

 🤔 TB_INVC_DTL 에서 invc_no 별 invc_qty별로 동일하기 떄문에 MAX로 묶었을때가 가능하다 1:1 매치가 가능한 컬럼은 MAX로 소환이 가능하다

         HAVING사용하기

-  HAVING 절은 GROUP BY 를 통해 데이터를 그룹핑 한 행에만 사용할 수 있다 

   WHERE문은 그룹핑 하기 전에 필터하는 거라면 HAVING은 그룹핑 후 필터를 적용할 수 있다. 

 

 

문제 : 'IN00041653' 의 상품 수량 구문을 활용해서 수량이  6 초과인 상품만 출력되록하자  

-- 상품 수량이 5이상인 IN00041653 코드의 인보이스들 중에 7이하인 데이터

SELECT a.invc_no, ad.prod_cd,MAX(ad.invc_qty) AS invc_qty
FROM TB_INVC as a (nolock)
INNER JOIN TB_INVC_DTL as ad (nolock) ON a.invc_no=ad.invc_no
INNER JOIN TB_STOCK as s (nolock) ON ad.prod_cd=s.prod_cd
where a.invc_no ='IN00041653'
and ad.invc_qty >= 5
GROUP BY ad.prod_cd, a.invc_no
HAVING MAX(ad.invc_qty)  <= 7

 

             과제 및 풀이

2022.10.06 - [개발/SQL] - SQL 미니 과제와 함께하는 쿼리 공부하기 -1 테이블 정의 , Join

SQL 미니 과제와 함께하는 쿼리 공부하기 -1 테이블 정의 , Join

위 링크에 있는  테이블 컬럼을 참고

-- 다음 테이블 TB_INVC, TB_INVC_DTL, TB_STOCK join을 헤서 group by 조건문으로 나타내시오.
-- 단, invc_qty는 group by 사용시 max함수를 이용해 나타낸다음 5보다 같거나 큰경우로 나타낼것
-- invc_no : IN00041653

정답 : 

 

1. MAX를 활용

TB_INVC_DTL 에서 invc_no 별 invc_qty별로 동일하기 떄문에 MAX로 묶었을때가 가능하다

SELECT a.invc_no, ad.prod_cd,MAX(ad.invc_qty) AS inv_qty
FROM TB_INVC as a (nolock)
INNER JOIN TB_INVC_DTL as ad (nolock) ON a.invc_no=ad.invc_no
INNER JOIN TB_STOCK as s (nolock) ON ad.prod_cd=s.prod_cd
where a.invc_no ='IN00041653'
and ad.invc_qty >= 5
GROUP BY ad.prod_cd, a.invc_no

 

 

-- pi_no를 묶어보기 
SELECT a.invc_no, ad.prod_cd,MAX(pi_no) AS inv_qty
FROM TB_INVC as a (nolock)
INNER JOIN TB_INVC_DTL as ad (nolock) ON a.invc_no=ad.invc_no
INNER JOIN TB_STOCK as s (nolock) ON ad.prod_cd=s.prod_cd
where a.invc_no ='IN00041653'
and ad.invc_qty >= 5
GROUP BY ad.prod_cd, a.invc_no

2. GROUP BY를 활용

SELECT a.invc_no, ad.prod_cd, ad.invc_qty
FROM TB_INVC as a (nolock)
INNER JOIN TB_INVC_DTL as ad (nolock) ON a.invc_no=ad.invc_no
INNER JOIN TB_STOCK as s (nolock) ON ad.prod_cd=s.prod_cd
where a.invc_no ='IN00041653'
and ad.invc_qty >= 5
GROUP BY ad.prod_cd, a.invc_no, ad.invc_qty