하나둘셋넷

SQL GROUP BY] 카테고리 별 도서 판매량 집계하기

-- 코드를 입력하세요
SELECT b.CATEGORY, sum(s.SALES) as TOTAL_SALES
    FROM BOOK b
    INNER JOIN(
        SELECT BOOK_ID, SALES, SALES_DATE
        FROM BOOK_SALES
    ) as s on b.BOOK_ID = s.BOOK_ID
    WHERE s.SALES_DATE >= '2022-01-01' AND s.SALES_DATE < '2022-02-01'
    GROUP BY b.CATEGORY
    ORDER BY b.CATEGORY

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

-- 코드를 입력하세요
SELECT u.USER_ID,	u.NICKNAME,	SUM(b.PRICE) as TOTAL_SALES
    FROM USED_GOODS_BOARD b # 중고 거래 게시판
    INNER JOIN USED_GOODS_USER u on u.USER_ID = b.WRITER_ID
    WHERE b.STATUS = 'DONE'
    GROUP BY u.USER_ID, u.NICKNAME
    HAVING SUM(b.PRICE) >= 700000
    ORDER BY TOTAL_SALES ASC

SQL GROUP BY] 즐겨찾기가 가장 많은 식당 정보 출력하기

1] FOOD_TYPE 별로 가장 인기가 많은 MAX(FAVORITES) 테이블 만들기

SELECT FOOD_TYPE, MAX(FAVORITES) AS MaxFavorites
        FROM REST_INFO
        GROUP BY FOOD_TYPE

(1) FROM REST_INFO

(2) GROUP BY FOOD_TYPE

2] 기존 테이블과 INNER JOIN

-- 코드를 입력하세요
SELECT r.FOOD_TYPE, r.REST_ID, r.REST_NAME, r.FAVORITES
    FROM REST_INFO r
    INNER JOIN(
        
        SELECT FOOD_TYPE, MAX(FAVORITES) AS MaxFavorites
        FROM REST_INFO
        GROUP BY FOOD_TYPE
        
    ) AS max_res ON r.FOOD_TYPE = max_res.FOOD_TYPE 
                    AND r.FAVORITES = max_res.MaxFavorites
    ORDER BY r.FOOD_TYPE DESC;

(1) FOOD_TYPE이 같고, 기존 테이블 FAVORITES =  최대값 테이블 MaxFavorites 같도록 합친다.

주의

-- 코드를 입력하세요
SELECT r.FOOD_TYPE, r.REST_ID, r.REST_NAME, MAX(r.FAVORITES)
    FROM REST_INFO r    
    GROUP BY r.FOOD_TYPE

GROUP BY에 속하지 않은 모든 컬럼은 집계 함수 내에 있어야 한다.