[프로그래머스] SQL고득점키트 - GROUP BY

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

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

---

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

SELECT MCDP_CD AS '진료과 코드', COUNT(APNT_YMD) AS 5월예약건수
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD,'%Y-%m') = '2022-05'
GROUP BY MCDP_CD
ORDER BY 5월예약건수, MCDP_CD

** 구문 순서

1. select 
2. where
3. group by
4. order by

---

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

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

**DISTINCT  : 중복 제거

 

---

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

 

처음에 맞는줄 알았는데 틀려서 오답노트 겸 정리해봤다

 

<내 첫 답> 

SELECT FOOD_TYPE, REST_ID, REST_NAME, MAX(FAVORITES) AS FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC

 ->FAVORITES에서 문제 생김 (가장 많은 즐겨찾기 수가 아닌 테이블에서 가장 상단의 식당 정보)

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

**서브 쿼리 : 테이블을 하나 더 만들기

 테이블 정의해주고 JOIN 하면 된다!

 

**주의 할 점 키를 두개 동시에 충족시키도록 해야함! , FAVORITES는 A!

---

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

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
GROUP BY CATEGORY) AS B
ON A.CATEGORY = B.CATEGORY AND A.PRICE = B.PRICE
WHERE A.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY A.PRICE DESC

---

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

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

** 순서 기억하기!!

SELECT

FROM

JOIN - ON

WHERE 

GROUP BY 

HAVING : WHERE과는 달리 이미 그룹된 것들을 조건을 설정한다!

ORDER! 

 

---

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

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) BETWEEN 8 AND 10
    GROUP BY CAR_ID
    HAVING COUNT(CAR_ID) >= 5
    )
AND YEAR(START_DATE) = 2022 AND MONTH(START_DATE) BETWEEN 8 AND 10
GROUP BY MONTH, CAR_ID
HAVING COUNT(CAR_ID) > 0
ORDER BY MONTH, CAR_ID DESC

- 서브 쿼리를 활용해 보았다!! 훨씬 편함

SQL 서브쿼리(Sub Query) 예제 - select절, from절, where절 (tistory.com)

SQL 서브쿼리(Sub Query) 예제 - select절, from절, where절

-> 이 포스트로 공부하였다.

특히, WHERE절 서브쿼리는 많은 경우에 쓰이므로 형식을 대략 알아두는 것이 좋은 것 같다

 

SELECT * 

FROM TABLE

WHERE col1 IN (SELECT col1 FROM TABLE WHERE~~)