하나둘셋넷

SQL GROUP BY] 년, 월, 성별 별 상품 구매 회원 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131532

-- 코드를 입력하세요
SELECT DATE_FORMAT(O.SALES_DATE, '%Y') as YEAR, DATE_FORMAT(O.SALES_DATE, '%m') as MONTH,
        GENDER, COUNT(DISTINCT(U.USER_ID)) as USERS
FROM USER_INFO U
INNER JOIN ONLINE_SALE O on U.USER_ID = O.USER_ID
WHERE GENDER IS NOT NULL 
GROUP BY YEAR, MONTH, U.GENDER
ORDER BY YEAR, MONTH, U.GENDER

SQL GROUP BY] 성분으로 구분한 아이스크림 총 주문량

https://school.programmers.co.kr/learn/courses/30/lessons/133026

-- 코드를 입력하세요
SELECT I.INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) as TOTAL_ORDER
FROM FIRST_HALF F
INNER JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE

SQL GROUP BY] 고양이와 개는 몇 마리 있을까

https://school.programmers.co.kr/learn/courses/30/lessons/59040

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) as count
    FROM ANIMAL_INS
    GROUP BY ANIMAL_TYPE
    ORDER BY ANIMAL_TYPE

SQL GROUP BY] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157339

SELECT C.CAR_ID, C.CAR_TYPE, ROUND((C.DAILY_FEE * 30 * (1 - D.DISCOUNT_RATE / 100.0))) AS FEE

FROM CAR_RENTAL_COMPANY_CAR C

INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE AND D.DURATION_TYPE = '30일 이상'

### '세단','SUV' 조건에 속하면서 11월에 대여중인 CAR_ID가 11월에 대여 중인 그룹에는 속하지 않아야 한다. ###
WHERE C.CAR_TYPE IN ('세단', 'SUV')
        AND C.CAR_ID NOT IN (
            SELECT H.CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
            WHERE (H.START_DATE <= '2022-11-30' AND H.END_DATE >= '2022-11-01')
        )
        
GROUP BY C.CAR_ID
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

SQL GROUP BY] 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151137

-- 코드를 입력하세요
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

SQL GROUP BY] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

SELECT G.MONTH, G.CAR_ID, COUNT(G.CAR_ID) AS RECORDS
FROM (
    SELECT CAR_ID, MONTH(START_DATE) AS MONTH
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
) G
INNER JOIN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY CAR_ID
    HAVING COUNT(CAR_ID) >= 5
) T ON G.CAR_ID = T.CAR_ID
GROUP BY G.CAR_ID, G.MONTH
ORDER BY G.MONTH, G.CAR_ID DESC;

SQL GROUP BY] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

https://school.programmers.co.kr/learn/courses/30/lessons/157340

SELECT CAR_ID,
       CASE
            # START와 DATE 사이에 최소 2022-10-16이 끼어있다면, 1보다 클 것이고 이 경우에 '대여중'으로 표시한다.
            # 한 번이라도 해당 날짜에 대여중인 상태가 있는지 확인한다.
            WHEN SUM(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1 ELSE 0 END) > 0
            THEN '대여중'
            ELSE '대여 가능' 
        END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

SQL GROUP BY] 저자 별 카테고리 별 매출액 집계하기

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에 속하지 않은 모든 컬럼은 집계 함수 내에 있어야 한다.