프로그래머스 SQL 고득점 Kit - GROUP BY

1. 즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM REST_INFO R JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
) AS F ON (R.FOOD_TYPE = F.FOOD_TYPE AND R.FAVORITES = F.FAVORITES)
ORDER BY R.FOOD_TYPE DESC

// 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문

 

- GROUP BY를 통해 음식 종류별 MAX FAVORITES 수를 구할 수 있고, 이를 기반으로 REST_INFO 테이블에서 해당하는 열을 찾으면 됨. 서브 쿼리와 INNER JOIN을 활용함

 

2. 조건에 맞는 사용자와 총 거래금액 조회하기

SELECT U.USER_ID, U.NICKNAME, B.TOTAL_PRICE
FROM USED_GOODS_USER U JOIN (
    SELECT WRITER_ID, SUM(PRICE) AS TOTAL_PRICE
    FROM USED_GOODS_BOARD
    WHERE STATUS = 'DONE'
    GROUP BY WRITER_ID
) AS B ON U.USER_ID = B.WRITER_ID
WHERE B.TOTAL_PRICE >= 700000
ORDER BY B.TOTAL_PRICE

// 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문

 

- 서브쿼리와 GROUP BY를 이용해 WRITER_ID별 거래금액 총액을 구하고, USER 테이블에 INNER JOIN시킨 후 조건에 맞는 행만 출력한다

SELECT U.USER_ID, U.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_USER U
JOIN USED_GOODS_BOARD B ON U.USER_ID = B.WRITER_ID
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID HAVING SUM(B.PRICE) >= 700000
ORDER BY TOTAL_SALES ASC

- 그냥 위처럼 JOIN 시켜두고 거기서 WHERE로 필터링, GROUP BY와 HAVING으로 이차 필터링을 거쳐도 됨

 

3. 저자 별 카테고리 별 매출액 집계하기

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE*T.SALES) AS SALES
FROM BOOK B
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
JOIN (
    -- 도서별 1월 판매량
    SELECT BOOK_ID, SUM(SALES) AS SALES
    FROM BOOK_SALES
    WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
    GROUP BY BOOK_ID
) AS T ON B.BOOK_ID = T.BOOK_ID
GROUP BY B.AUTHOR_ID, B.CATEGORY
ORDER BY B.AUTHOR_ID ASC, B.CATEGORY DESC

// 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문

 

- 일단 서브쿼리를 통해 각 BOOK_ID별 1월 판매량을 구함

- 기존 BOOK 테이블에 AUTHOR_NAME 컬럼을 합치고, BOOK_ID별 1월 판매량 컬럼도 합침

- AUTHOR_ID와 CATEGORY 기준 그룹을 나누고 판매액 총합을 구하면 

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE*BS.SALES) AS SALES
FROM BOOK B
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
WHERE BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC

- 굳이 서브쿼리를 쓰지 않고도 일단 다 jOIN해놓고 차례차례 필터링과 그룹핑을 적용해도 됨

 

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

SELECT B.CATEGORY, SUM(S.SALES) AS TOTAL_SALES
FROM BOOK B
JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE S.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY B.CATEGORY
ORDER BY CATEGORY ASC

 

 

5. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT CAR_ID, MAX(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, "대여중", "대여 가능")) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

// 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문

 

- IF(조건문, 참일 때, 거짓일 때) 이용하여 별도 열을 만듦, CAR_ID 기준 그룹화를 함

- CASE - WHEN - THEN - WHEN - THEN - ELSE - END 이런 문법을 사용해도 

- 동일한 CAR_ID에 대해 대여중이 하나라도 있으면 최종적인 상태가 대여중 나오도록, 그룹 내 문자열에 대해서 MAX/MIN 적용하여 문자열 우선순위 활용함

SELECT MAX(B.A)
FROM (SELECT "대여중" AS A UNION SELECT "대여 가능" AS A) B

// 혹시 몰라 테스트돌려봤는데, 예상대로 대여중이 나오더라

 

6. 진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD AS "진료과 코드", COUNT(*) AS "5월 예약 건수"
FROM APPOINTMENT
WHERE APNT_YMD BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY MCDP_CD
ORDER BY COUNT(*) ASC, MCDP_CD ASC

// 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문

 

- MySQL에서는 별칭을 따옴표로 감싸지 않고 사용, 하지만 공백이나 특수문자가 사용된 별칭의 경우 불가능

- ORDER BY `5월 예약 건수` ASC, MCDP_CD ASC 이렇게 백틱으로 감싸니까 별칭 인식 제대로 됨

 

7. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE "%통풍시트%" OR OPTIONS LIKE "%열선시트%" OR OPTIONS LIKE "%가죽시트%"
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

// CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문

 

- REGEXP 정규식 활용한다면 WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트' 이렇게 한 줄로 표현 가능

- '^ab' (ab로 시작), 'ab$' (ab로 끝남), 'a|b' (a 또는 b를 포함), 'ab*' (a 뒤에 b가 0번 이상 반복), '[abc]' (a,b,c 중 하나를 포함하는 문자열), '[^abc]' (a,b,c 제외한 다른 문자를 포함하는 문자열), 'a.c' (a와 c 사이에 문자 한 개)

 

8. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
AND CAR_ID IN (
    -- 총 대여횟수 5번 이상인 차들
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID HAVING COUNT(*) >= 5
)
GROUP BY MONTH, CAR_ID HAVING COUNT(*)>0
ORDER BY MONTH ASC, CAR_ID DESC

// 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문

 

- 서브쿼리를 통해 대여횟수 5번 이상인 자동차들 ID만 골라냄, 대여 시작일 기준/CAR_ID 기준 필터링 적용 후 그룹화

- 특정 월의 총 대여횟수가 0인 경우 제거하기 위해 HAVING COUNT(*)>0 사용

 

9. 성분으로 구분한 아이스크림 총 주문량

SELECT I.INGREDIENT_TYPE, SUM(H.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF H
JOIN ICECREAM_INFO I ON H.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

// 상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문

 

- FLAVOR가 각각 고유키/외래키였으므로 이 친구를 기준으로 INNER JOIN 하면 됨

 

10. 식품분류별 가장 비싼 식품의 정보 조회하기

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

// 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문

 

11. 고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

 

12. 동명 동물 수 찾기

SELECT NAME, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME HAVING COUNT(*) >= 2
ORDER BY NAME ASC

// 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성

 

- 이름 없는 동물은 집계에서 제외하기 위해 NAME IS NOT NULL 구문을 추가함

 

13. 년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, U.GENDER, COUNT(DISTINCT S.USER_ID) AS USERS
FROM ONLINE_SALE S
JOIN USER_INFO U ON S.USER_ID = U.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR(S.SALES_DATE), MONTH(S.SALES_DATE), U.GENDER
ORDER BY YEAR ASC, MONTH ASC, U.GENDER ASC

// 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문 작성, 이 때 성별 정보 없는 경우 생략

 

- 일단 USER_ID 별로 INNER JOIN하고 세 가지 기준으로 grouping

- 회원수 집계할 때는 DISTINCT 이용하여 중복된 ID 제거하고 카운

 

14. 입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

// 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문

 

15. 입양 시각 구하기(2)

SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR

// 이렇게 하려고 했으나, 입양이 0건인 시간대는 표시되지 않음

 

- MySQL에서 사용자 정의 변수 선언 및 초기화는 = 연산자로 한다, SET 이외의 명령문에서는 =는 비교연산자 취급 받기에 := 로 선언해주어야 함, @를 앞에 붙인다

SET @HOUR = -1; 또는 SET @HOUR := -1;
SELECT @HOUR := @HOUR+1;
SELECT * FROM WORKER WHERE W_ID BETWEEN @START AND @FINISH;

 

따라서 반복문 변수처럼 @HOUR를 0~23까지 증가시키고, 서브쿼리를 이용해 개수 세면 됨

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

 

혹은 아래와 같이 해도 된다.

0~23까지 행이 있는 테이블에, 맨 위에서 GROUP BY로 구한 시간대/카운트 테이블을 LEFT JOIN하고, 카운트 열이 NULL이면 0으로 대체해주는 방식으로

SET @HOUR = -1;
SELECT T1.HOUR, IFNULL(T2.COUNT, 0) AS COUNT
FROM (
    SELECT (@HOUR := @HOUR +1) AS HOUR
    FROM ANIMAL_OUTS
    WHERE @HOUR < 23
) AS T1
LEFT JOIN (
    SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
) AS T2 ON T1.HOUR = T2.HOUR

 

16. 가격대 별 상품 개수 구하기

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC

// 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문

 

- CEIL(숫자)는 소수점 이하 올림, FLOOR(숫자)는 소수점 이하 내림, ROUND(숫자, 자릿수) 와 TRUNCATE(숫자, 자릿수) 는 각각 자릿수 기준으로 반올림 또는 버림 (자릿수로 음수도 가능하다!!)