2023-03-03 금요일

💡 가격대 별 상품 개수 구하기

---

🎯 첫번째. CASE 문 활용

1. PRODUCT 테이블 전체 출력하기

SELECT
*
 FROM PRODUCT

💻 출력

---

2. 가격대 별 상품 개수 출력하기

SELECT
    (CASE
        WHEN 10000<=price AND price<20000 THEN '10000'
        WHEN 20000<=price AND price<30000 THEN '20000'
        WHEN 30000<=price AND price<40000 THEN '30000'
        WHEN 40000<=price AND price<50000 THEN '40000'
        WHEN 50000<=price AND price<60000 THEN '50000'
        WHEN 60000<=price AND price<70000 THEN '60000'
        WHEN 70000<=price AND price<80000 THEN '70000'
        WHEN 80000<=price AND price<90000 THEN '80000'
    END) AS PRICE_GROUP
    , COUNT(product_id) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP

💻 출력

---

3. 가격대 기준으로 오름차순 정렬하기

SELECT
    (CASE
        WHEN 10000<=price AND price<20000 THEN '10000'
        WHEN 20000<=price AND price<30000 THEN '20000'
        WHEN 30000<=price AND price<40000 THEN '30000'
        WHEN 40000<=price AND price<50000 THEN '40000'
        WHEN 50000<=price AND price<60000 THEN '50000'
        WHEN 60000<=price AND price<70000 THEN '60000'
        WHEN 70000<=price AND price<80000 THEN '70000'
        WHEN 80000<=price AND price<90000 THEN '80000'
    END) AS PRICE_GROUP
    , COUNT(product_id) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC

💻 출력

---

🎯 두번째. TRUNCATE 함수 활용

1. PRODUCT 테이블 전체 출력하기

SELECT
*
 FROM PRODUCT

💻 출력

---

2. TRUNCATE를 활용하여 -4자리수 위치까지 버리기

SELECT TRUNCATE(price, -4) AS PRICE_GROUP
        , COUNT(product_id) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP

💻 출력

---

3. 가격대 기준으로 오름차순 정렬하기

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

💻 출력

---