[programmers] GROUP BY

---

진료과별 총 예약 횟수 출력하기 - level 2

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/132202

• GROUP BY, WHERE 절을 이용한다.

  Sol) 

sql

열기 줄바꿈

SELECT MCDP_CD '진료과코드', COUNT(*) '5월예약건수'
FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = 2022 AND MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY 2, 1

 

---

식품 분류별 가장 비싼 식품의 정보 조회하기 - level 4

  출처 - https://school.programmers.co.kr/learn/courses/30/lessons/131116

  cf) 정답
# 식용 마저유: 8950
# 김치 배추김치: 19000
# 국 김치찌개: 2900
# 과자 허니버터칩: 1950

 

  Sol 1) CTE 방식 - FROM 서브 쿼리와 같음

• A: 카테고리별 최대 금액, 상품 ID 정보를 담고 있다. 
• B: 전체 테이블이며 여기에서 상품 이름을 조회해야한다. 
• A의 최대 금액(M)과 B의 상품 금액이 같은 것만 조회해야한다. 

sql

열기 줄바꿈

WITH A AS
(
    SELECT PRODUCT_ID, CATEGORY, MAX(PRICE) M
    FROM FOOD_PRODUCT  
    GROUP BY CATEGORY 
 )
SELECT A.CATEGORY, M, B.PRODUCT_NAME
FROM A JOIN FOOD_PRODUCT B USING (CATEGORY)
WHERE M = PRICE
    AND A.CATEGORY IN ("과자", "국", "김치", "식용유")   
GROUP BY CATEGORY  
ORDER BY 2 DESC

 

 

  Sol 2) WHERE 절 서브 쿼리

•  

sql

열기 줄바꿈

SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT F
WHERE CATEGORY IN ('식용유', '김치', '국','과자')
    AND PRICE  = (
        SELECT MAX(PRICE)
        FROM FOOD_PRODUCT
        WHERE CATEGORY = F.CATEGORY
        GROUP BY CATEGORY
    )
GROUP BY 1
ORDER BY 2 DESC

 

 

  Sol 3) WHERE 절 서브 쿼리

• 참고) https://amyyzzin.tistory.com/417

sql

열기 줄바꿈

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT 
WHERE CATEGORY IN ('식용유', '김치', '국','과자')
    AND PRICE IN (
        SELECT MAX(PRICE)
        FROM FOOD_PRODUCT
        GROUP BY CATEGORY
        )
GROUP BY 1
ORDER BY PRICE DESC

 

• 아래와 같이 PRICE IN() 으로 비교를 하고 내부 쿼리에 CATEGORY를 비교하지 않더라도 통과
• 카테고리를 비교하지 않았는데  어떻게 맞는지 모르겠다
  • 프로그래머스는 테스트 케이스가 하나인데 row 들이 중복되지 않아서 문제 없도록 구성되어 있다.
  • 정확하게 따지면 price 를 등호로 비교하고 where 절로 비교하는 것이 정확하다. 
• IN() 안에는 카테고리별 최대 금액이 모두 들어가 있다. 

 

• Ex) 카테고리 비교하지 않을 때 문제점 
  • 만약 식용유, 김치 가격이 이렇게 구성된다면?
    • 식용유: 4500 5000
    • 김치:  4000  4500
    • 이라고 하면 4500원 짜리 식용유는 김치 MAX 와 같다. 
    • 이런 식으로 금액이 다른 카테고리의 MAX 값과 겹치는 경우는 아래 쿼리가  오답이 될 수 있다.
      • 프로그래머스의 테스트 케이스 테이블을 보면 네 개의  max 집합 = { 1950, 2900, 19000,  8950} 인데 
      • a) 김치 중 max 아닌 값: { 16950, 17000, 17500} 
      • b) 식용유  중 max 아닌 값  : {4880, 5950, 7200}
      • c ) 국  중 max 아닌 값  : { 2400, 2450, 2700}
      • d) 과자  중 max 아닌 값 : {1500, 1800, 1900}
      • a, b, c, d 중에서 max 집합에 속하는 값이 없기 떄문에 테스트를 통과한거 아닌가?
      • 다시 말해서, {a, b, c, d } 중에서 max 집합과 교집합이 있는 테스트 케이스가 나오면 오답일 수도 있을 것 같다. 
      • 왜냐하면 카테고리를 비교하지 않기 때문

 

---

즐겨찾기가 가장 많은 식당 정보 출력하기 - level 3

출처 -  https://school.programmers.co.kr/learn/courses/30/lessons/131123

 

  Sol ) 

• 가장 먼저 MAP 처럼 {음식타입, 즐겨찾기 최댓값} 만으로 테이블을 만든다. 
• 그리고 원래의 테이블과 비교하면서 즐겨찾기 값이 같은 경우만 골라서 출력하도록 WHERE 절 조건을 넣어준다. 

sql

열기 줄바꿈

WITH MAP AS
(
SELECT FOOD_TYPE, MAX(FAVORITES) 최댓값
FROM REST_INFO 
GROUP BY FOOD_TYPE
)
SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM MAP JOIN REST_INFO  R USING (FOOD_TYPE)
WHERE 최댓값 = FAVORITES
ORDER BY 1 DESC

 

---

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 - level 3

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/151139

 

• GROUP BY CAR_ID, MONTH .. 이런 식으로 만들어 줘야한다. 

 

  Sol) 

• 대여 기간은 시작일(START_DATE)을 기준으로 한다. 
• SUB_H: 전체 테이블과 JOIN 하기 위해 만드는 테이블이며 자동차별로 해당 기간에 대여한 횟수를 COUNT 해서 반환한다. 
  • 따라서 이 테이블은 대여 횟수 5 이상인 자동차 ID 정보만을 가져오기 위해서 필요한 테이블이다. 
• H: 전체 테이블
• SUB_H 테이블에서 대여 횟수가 5이상인 데이터만 가져온 다음에, H 테이블에서도 기간 조건을 만족하는 데이터를 가져와서 JOIN 한다. 두 개의 테이블에 기간 조건을 공통적으로 넣어줘야한다. 
• 아래 블로그 내용을 참고해서 조금 변형해서 풀었다. 

 

출처 - https://amyyzzin.tistory.com/411

sql

열기 줄바꿈

WITH SUB_H AS(
    SELECT CAR_ID, COUNT(*) 차량별대여횟수
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE YEAR(START_DATE) = 2022
        AND MONTH(START_DATE) >= 8
        AND MONTH(START_DATE) <= 10
    GROUP BY 1
)
SELECT MONTH(H.START_DATE), H.CAR_ID,  COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN SUB_H USING (CAR_ID)
WHERE 차량별대여횟수 >= 5
    AND YEAR(START_DATE) = 2022
    AND MONTH(START_DATE) >= 8
    AND MONTH(START_DATE) <= 10
GROUP BY 2, 1
ORDER BY 1, 2 DESC

 

---

카테고리별 도서 판매량 집계하기

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/144855

 

 

 Sol) 

• JOIN해서 각 카테고리에 대한 모든 판매량을 더해준다. 

sql

열기 줄바꿈

SELECT CATEGORY, SUM(SALES) TOTAL_SALES
FROM BOOK B JOIN BOOK_SALES BS USING (BOOK_ID)
WHERE YEAR(SALES_DATE) = 2022 
    AND MONTH(SALES_DATE) = 1
GROUP BY CATEGORY 
ORDER BY 1

 

---

성분으로 구분한 아이스트림 총 주문량

출처  - https://school.programmers.co.kr/learn/courses/30/lessons/133026 

 

  Sol)

sql

열기 줄바꿈

SELECT INGREDIENT_TYPE , SUM(TOTAL_ORDER) 
FROM FIRST_HALF RIGHT JOIN ICECREAM_INFO ICE USING (FLAVOR)
GROUP BY INGREDIENT_TYPE

• group by 로 나눠서 주문량의 총합을 구한다. 

 

---

자동차 종류별 특정 옵션이 포함된 자동차 수 구하기 - level 2

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/151137 

 

  Sol)

sql

열기 줄바꿈

SELECT  CAR_TYPE, count(*)
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE('%통풍시트%')
    OR OPTIONS LIKE('%열선시트%')
    OR OPTIONS LIKE('%가죽시트%')
GROUP BY CAR_TYPE
ORDER BY 1

• GROUP BY
• LIKE() 안에 와일드 카드를 넣어서 조건을 만든다. 

---

고양이와 개는 몇 마리 있을까 - level 2

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59040 

 

  Sol)

sql

열기 줄바꿈

SELECT ANIMAL_TYPE, COUNT(*)
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE
ORDER BY 1

 

---

동명 동물 수 찾기 - level 2

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59041 

 

  Sol)

sql

열기 줄바꿈

WITH TMP AS
(
    SELECT NAME, COUNT(*) CNT
    FROM ANIMAL_INS 
    WHERE NAME IS NOT NULL
    GROUP BY NAME
)
SELECT  *
FROM TMP
WHERE CNT > 1
ORDER BY 1

• where절에 is not null을 꼭 추가해야한다. 

 

---

입양 시각 구하기 (1) - level 2

출처  - https://school.programmers.co.kr/learn/courses/30/lessons/59412

 

  Sol)

sql

열기 줄바꿈

WITH TMP AS
(
    SELECT ANIMAL_ID, date_FORMAT(DATETIME, '%H')  HOUR
    FROM ANIMAL_OUTS 
)
SELECT HOUR, COUNT(ANIMAL_ID)
FROM TMP JOIN ANIMAL_OUTS USING (ANIMAL_ID)
WHERE HOUR >= 9 AND HOUR < 20
GROUP BY HOUR
ORDER BY 1

• 동일한 두 개의 테이블을 조인해서 가져온다. 
• PK인 ANIMAL_ID를 사용해서 조인한다. 
• COUNT(ANIMAL_ID)를 해주면 해당하는 시간에 맞는 로우를 카운트한다. 
• 시간 형식은 DATE_FORMAT()을 사용한다. 

 

---

가격대 별  상품 개수 구하기 - level 2

출처  - https://school.programmers.co.kr/learn/courses/30/lessons/131530

  Sol)

sql

열기 줄바꿈

SELECT  PRICE - PRICE % 10000 PRICE_GROUP, COUNT(*)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY 1

• 가격을 의미하는 PRICE에서 10000으로 나눈 나머지를 빼면  PRICE_GROUP를 구할 수 있다. 

 

---

조건에 맞는 사용자와 총 거래금액 조회하기- level 3

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/164668

  Sol)

sql

열기 줄바꿈

WITH TMP AS
(
    SELECT *, SUM(PRICE) TOTAL_SALES
    FROM USED_GOODS_BOARD JOIN USED_GOODS_USER ON WRITER_ID = USER_ID
    WHERE STATUS = "DONE"
    GROUP BY USER_ID
)
SELECT USER_ID, NICKNAME, TOTAL_SALES
FROM TMP 
WHERE TOTAL_SALES >= 700000
ORDER BY 3

• 임시 쿼리 안에 총 거래 금액을 구하고  WHERE 절 조건으로 DONE 상태인 로우만 가져오도록 쿼리를 짠다. 

 

---

자동차 대여 기록에서 대여 중 / 대여 가능 여부 구분하기 - level 3

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/157340  

  Sol)

sql

열기 줄바꿈

WITH TMP AS
(
    SELECT CAR_ID, 
       CASE
         WHEN START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE THEN 1
         ELSE 0
         END AVAILABILITY_N
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
)
SELECT CAR_ID, 
    CASE
        WHEN SUM(AVAILABILITY_N) = 1 THEN '대여중'
        ELSE '대여 가능'
        END 
FROM TMP
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

• tmp: 대여중인 car에 대해서만 1을 넣어주고 다른 행은 모두 0으로 넣어준다. 
• 본 쿼리에서는 GROUP BY를 이용해서 위에 숫자로 표현한 값을 모두 더해주고 더한 값이 1인 경우는 "대여중", 그 외의 경우는 "대여 가능"으로 표시한다. 

---

저자 별 카테고리 별 매출액 집계하기  - level 4

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/144856

  Sol)

sql

열기 줄바꿈

SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY,
SUM(SALES * PRICE) TOTAL_SALES
FROM AUTHOR JOIN BOOK USING (AUTHOR_ID)
            JOIN BOOK_SALES USING (BOOK_ID) 
WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 1
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY 1, 3 DESC

• GROUP BY 조건을 이중으로 넣는다. 
• WHERE 절에도 연도, 월에 대한 조건을 반드시 넣어야한다. 

---

년, 월, 성별 별 상품 구매 회원 수 구하기-  level 4

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/131532  

  Sol)

• DISTINCT를 꼭 넣어줘야한다. 넣지 않으면 같은 USER_ID가 중복되서 카운트되기 때문에 주의한다. 
• GROUP BY로 세 번 중첩해서 그룹을 나눠준다. 

sql

열기 줄바꿈

SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, GENDER
, COUNT(DISTINCT USER_ID) USERS
FROM ONLINE_SALE JOIN USER_INFO USING (USER_ID)
WHERE GENDER IS NOT NULL
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER
ORDER BY 1, 2, 3

 

---

입양 시각 구하기 (2) -  level 4

출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59413  

  Sol)

sql

열기 줄바꿈

SET @HOUR = -1;
SELECT @HOUR := @HOUR + 1, 
    (SELECT COUNT(*)
     FROM ANIMAL_OUTS
     WHERE  @HOUR = HOUR(DATETIME)
    )
FROM ANIMAL_OUTS
WHERE @HOUR < 23

• 행 번호를 생성해야한다. 
• set @hour = - 1;   변수를 생성한다.  
• SELECT @hour := @hour + 1    FROM ANIMAL_OUTS     WHERE @hour < 23
  • 서브쿼리를 이용해서 외부 쿼리와 시간이 같은 경우에 대해서 COUNT() 해준다.