[SQL] 프로그래머스 GROUP BY

 

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

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

 

다른사람 풀이를 보니

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

TIMESTAMP를 LIKE '2022-05%' 로 표현하는 방법도 있었다.

 

---

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

SELECT I.INGREDIENT_TYPE as "INGREDIENT_TYPE", SUM(F.TOTAL_ORDER) as "TOTAL_ORDER"
FROM FIRST_HALF as F, ICECREAM_INFO as I
WHERE F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY F.TOTAL_ORDER
# SELECT * FROM FIRST_HALF

 

 

---

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

SELECT b.CATEGORY, SUM(bs.SALES) as TOTAL_SALES
FROM BOOK b, BOOK_SALES bs
WHERE b.BOOK_ID = bs.BOOK_ID
AND  bs.SALES_DATE like "2022-01%"
GROUP BY CATEGORY
ORDER BY CATEGORY

---

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

틀린 코드

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

 

 

정답 코드

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

 

먼저 FOOD_TYPE 그룹 별로 가장 FAVORITES가 높은 쿼리를 찾아 놓고, 그 상태에서 쿼리를 출력해야 함

 

 

 

---

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

 

JOIN은 생각이 안나서 그냥 key매칭 시켜서 풀었다.

SELECT b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY, SUM(SALES*PRICE) as TOTAL_SALES
FROM BOOK b, AUTHOR a, BOOK_SALES bs
WHERE b.AUTHOR_ID = a.AUTHOR_ID
and b.BOOK_ID = bs.BOOK_ID
and bs.SALES_DATE LIKE "2022-01%"
GROUP BY a.AUTHOR_NAME, b.CATEGORY
ORDER BY a.AUTHOR_ID, CATEGORY DESC

 

그런데 SUM(SALES*PRICE) 이랑 SUM(SALES)*PRICE 결과값이 같은 것 같은데 왜 SUM(SALES)*PRICE이건 답이 아닐까...? 잘 모르겠다..

 

 

 

 

 

 

---

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

SELECT CATEGORY, PRICE as MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
    SELECT CATEGORY, MAX(PRICE)
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
)
and CATEGORY IN ("과자", "국", "김치", "식용유")
ORDER BY PRICE DESC

 

 

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

SELECT YEAR(o.SALES_DATE), MONTH(o.SALES_DATE), GENDER, COUNT(DISTINCT(u.USER_ID))
FROM USER_INFO u, ONLINE_SALE o
WHERE u.USER_ID = o.USER_ID
and GENDER IS NOT NULL
GROUP BY YEAR(o.SALES_DATE), MONTH(o.SALES_DATE), GENDER   
ORDER BY YEAR(o.SALES_DATE), MONTH(o.SALES_DATE), GENDER

DISTINCT를 안해줘서 틀렸다 ㅠㅠ