[SQL Kit] SELECT & GROUP BY

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

WITH MAX_DATA AS 
 (SELECT MAX(FAVORITES) AS MAX_FAVORITES
       , FOOD_TYPE
    FROM REST_INFO
   GROUP BY FOOD_TYPE)
SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES
  FROM REST_INFO A
  JOIN MAX_DATA B
    ON A.FOOD_TYPE = B.FOOD_TYPE
   AND A.FAVORITES = B.MAX_FAVORITES
 ORDER BY FOOD_TYPE DESC
 
 
SELECT FOOD_TYPE
     , REST_ID
     , REST_NAME
     , FAVORITES 
FROM (
       SELECT FOOD_TYPE
            , REST_ID
            , FAVORITES
            , REST_NAME 
            , RANK() OVER(PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) RANK 
         FROM REST_INFO
      ) 
 WHERE RANK = 1 
 ORDER BY FOOD_TYPE DESC

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

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

3. 동명 동물 수 찾기

SELECT NAME
      , COUNT(NAME) AS COUNT
   FROM ANIMAL_INS
  WHERE NAME IS NOT NULL
 HAVING COUNT(NAME) > 1
  GROUP BY NAME
  ORDER BY NAME

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

SELECT TO_CHAR(SALES_DATE, 'YYYY') AS YEAR
      , TO_CHAR(SALES_DATE, 'MM') AS MONTH
      , B.GENDER
      , COUNT(DISTINCT A.USER_ID) AS USERS
   FROM ONLINE_SALE A
   JOIN USER_INFO B
     ON A.USER_ID = B.USER_ID
  WHERE B.GENDER IS NOT NULL
  GROUP BY TO_CHAR(SALES_DATE, 'YYYY'), TO_CHAR(SALES_DATE, 'MM'), GENDER
  ORDER BY YEAR, MONTH, GENDER
  
  
  SELECT EXTRACT(YEAR FROM O.SALES_DATE) "YEAR"
       , EXTRACT(MONTH FROM O.SALES_DATE) "MONTH" 
       , U.GENDER
       , COUNT(DISTINCT O.USER_ID) "USERS"
    FROM USER_INFO U 
    JOIN ONLINE_SALE O
      ON U.USER_ID = O.USER_ID
   WHERE U.GENDER IS NOT NULL
   GROUP BY EXTRACT(YEAR FROM O.SALES_DATE)
       , EXTRACT(MONTH FROM O.SALES_DATE)
       , U.GENDER
   ORDER BY 1, 2, 3;

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

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

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

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

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

SELECT B.INGREDIENT_TYPE
     , SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
  FROM FIRST_HALF A
  JOIN ICECREAM_INFO B 
    ON A.FLAVOR = B.FLAVOR
 GROUP BY INGREDIENT_TYPE
 ORDER BY TOTAL_ORDER

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

 
 SELECT B.CATEGORY
     , SUM(A.SALES) AS TOTAL_SALES
  FROM BOOK_SALES A
  JOIN BOOK B
    ON A.BOOK_ID = B.BOOK_ID
 WHERE TO_CHAR(A.SALES_DATE, 'YYYYMM') = '202201'
 GROUP BY B.CATEGORY
 ORDER BY CATEGORY

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

 
SELECT MCDP_CD AS "진료과 코드"
     , COUNT(DISTINCT PT_NO)  AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'YYYYMM') = '202205'
GROUP BY MCDP_CD
ORDER BY 2, 1

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

 
 SELECT B.AUTHOR_ID
      , B.AUTHOR_NAME
      , A.CATEGORY
      , SUM(C.SALES * A.PRICE) AS TOTAL_SALES
  FROM BOOK A
  JOIN AUTHOR B
    ON A.AUTHOR_ID = B.AUTHOR_ID
  JOIN BOOK_SALES C
    ON A.BOOK_ID = C.BOOK_ID
 WHERE TO_CHAR(C.SALES_DATE, 'YYYYMM') = '202201'
 GROUP BY B.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY
 ORDER BY AUTHOR_ID, CATEGORY DESC