[SQL Kit] GROUP BY

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

-- 내가 작성한 쿼리
SELECT CAR_ID
     , DECODE(SUM(CASE WHEN TO_DATE('20221016', 'YYYYMMDD') BETWEEN START_DATE AND END_DATE
                  THEN 1 ELSE 0 END), 1, '대여중', 0, '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

-- 보완 쿼리
SELECT CAR_ID
     , MAX(CASE WHEN TO_DATE('20221016', 'YYYYMMDD') BETWEEN START_DATE AND END_DATE THEN '대여중'
                ELSE '대여 가능' END) AS AVAILABILITY
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 GROUP BY CAR_ID
 ORDER BY CAR_ID DESC

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

SELECT EXTRACT(MONTH FROM START_DATE) AS MONTH
     , CAR_ID
     , COUNT(CAR_ID) AS RECORDS
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 WHERE CAR_ID IN (
                     -- 대여시작일 기준 2022년 8월부터 2022년 10월까지 총 대여횟수가 5회 이상인 자동차ID 리스트
                     SELECT CAR_ID
                       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                      WHERE START_DATE BETWEEN TO_DATE('20220801', 'YYYYMMDD') AND TO_DATE('20221031', 'YYYYMMDD')
                     HAVING COUNT(CAR_ID) >= 5
                      GROUP BY CAR_ID
                  )
   AND START_DATE BETWEEN TO_DATE('20220801', 'YYYYMMDD') AND TO_DATE('20221031', 'YYYYMMDD')
HAVING COUNT(CAR_ID) > 0
 GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
 ORDER BY MONTH, CAR_ID DESC

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

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

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

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


SELECT HOUR
     , COUNT(HOUR) AS COUNT
  FROM ( 
          SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) AS HOUR
            FROM ANIMAL_OUTS
        )
 GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
 ORDER BY HOUR

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

-- 내가 한거...왜이렇게 복잡하게 했지 ..?
SELECT A.PRICE_GROUP
     , COUNT(B.PRODUCT_ID) AS PRODUCTS
  FROM (
          SELECT DECODE(0 + (ROWNUM -1), 0, 0, 0 + (ROWNUM -1) || '0000') AS PRICE_GROUP
            FROM DUAL
         CONNECT BY LEVEL <= (
                                SELECT SUBSTR(MAX(PRICE), 0, 1) +1
                                  FROM PRODUCT 
                              )
        ) A
  LEFT OUTER JOIN
       (
          SELECT TRUNC(PRICE, -4) AS PRICE
               , PRODUCT_ID
            FROM PRODUCT
        ) B
    ON A.PRICE_GROUP = B.PRICE
 GROUP BY PRICE_GROUP
 ORDER BY PRICE_GROUP
 
 
 -- 다른 풀이
  SELECT TRUNC(PRICE, -4) AS PRICE_GROUP
      , COUNT(PRODUCT_ID) AS PRODUCTS
   FROM PRODUCT
  GROUP BY TRUNC(PRICE, -4)
  ORDER BY PRICE_GROUP ASC

6. 우유와 요거트가 담긴 장바구니

-- 내가 처음에 한거... ㅎ 왜이렇게했징
SELECT DISTINCT A.CART_ID
  FROM (
            SELECT CART_ID
              FROM CART_PRODUCTS
             WHERE NAME IN ('Milk')
        ) A
  JOIN (
            SELECT CART_ID
              FROM CART_PRODUCTS
             WHERE NAME IN ('Yogurt')
        ) B
    ON A.CART_ID = B.CART_ID
 ORDER BY CART_ID
 
 
 -- 다른풀이 
  SELECT CART_ID
   FROM (
           SELECT DISTINCT CART_ID, NAME
             FROM CART_PRODUCTS
            WHERE NAME IN ('Milk','Yogurt')
         )
  GROUP BY CART_ID
 HAVING COUNT(*) >= 2
  ORDER BY CART_ID