[프로그래머스/SQL 고득점 Kit/MySQL] GROUP BY

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

프로그래머스

---

<GROUP BY>
Q.즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO 
WHERE (FOOD_TYPE,FAVORITES) IN (SELECT FOOD_TYPE,MAX(FAVORITES)
                FROM REST_INFO
                GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC

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

SELECT 
    au.AUTHOR_ID
    , au.AUTHOR_NAME
    , bo.CATEGORY
    , SUM(SALES*PRICE) as TOTAL_SALES
FROM BOOK_SALES as bs
INNER JOIN BOOK as bo ON bs.BOOK_ID = bo.BOOK_ID 
INNER JOIN AUTHOR as au ON bo.AUTHOR_ID = au.AUTHOR_ID
WHERE YEAR(bs.SALES_DATE)=2022
AND MONTH(bs.SALES_DATE)=1
GROUP BY au.AUTHOR_NAME, bo.CATEGORY 
ORDER BY au.AUTHOR_ID , bo.CATEGORY DESC

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

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

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

/*
SELECT WRITER_ID
FROM USED_GOODS_BOARD
WHERE STATUS='DONE'
GROUP BY WRITER_ID
HAVING SUM(PRICE)>=700000
*/
SELECT ugb.WRITER_ID, ugu.NICKNAME, SUM(ugb.PRICE) as TOTAL_SALES
FROM USED_GOODS_BOARD as ugb
JOIN USED_GOODS_USER  as ugu ON ugb.WRITER_ID = ugu.USER_ID
WHERE STATUS='DONE'
GROUP BY ugb.WRITER_ID
HAVING SUM(ugb.PRICE)>=700000
ORDER BY TOTAL_SALES

 

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

SELECT 
     ii.INGREDIENT_TYPE as INGREDIENT_TYPE
    ,SUM(TOTAL_ORDER) as TOTAL_ORDER 
FROM FIRST_HALF as fh
LEFT JOIN ICECREAM_INFO as ii ON fh.FLAVOR=ii.FLAVOR
GROUP BY ii.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER

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

SELECT
    b.CATEGORY as CATEGORY
    ,SUM(SALES) as TOTAL_SALES
FROM BOOK_SALES as bs
LEFT JOIN BOOK as b ON bs.BOOK_ID = b.BOOK_ID
WHERE YEAR(bs.SALES_DATE)=2022 and MONTH(bs.SALES_DATE)=1
GROUP BY b.CATEGORY
ORDER BY CATEGORY

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

SELECT 
    MCDP_CD as 진료과코드
    ,COUNT(*) as 5월예약건수
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD,"%Y-%m-%d") LIKE "2022-05-%"
GROUP BY MCDP_CD
ORDER BY 5월예약건수,진료과코드

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

SELECT 
    CAR_ID
    ,MAX(
    #CASE WHEN ('2022-10-16' between DATE_FORMAT(START_DATE,"%Y-%m-%d") and DATE_FORMAT(END_DATE,"%Y-%m-%d") )
    CASE WHEN ('2022-10-16' between START_DATE and END_DATE )
    THEN '대여중'
    ELSE '대여 가능' END
    ) as AVAILABILITY
    #,MAX(if('2022-10-16' between START_DATE and END_DATE, '대여중','대여 가능'))
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID #CAR_ID별로 기록하기 위함
ORDER BY CAR_ID DESC
/*
MAX()함수는 GROUP BY와 함께 사용해야함
GROUP BY를 통해 CAR_ID 기준으로 묶어주고, 
위 조건을 MAX() 통해 반환하면
, 같은 CAR_ID 중에 가장 큰 값이 실행 결과로 나타난다.
*/

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

SELECT MONTH(START_DATE) as MONTH, CAR_ID, COUNT(HISTORY_ID) as RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY  
WHERE CAR_ID IN (
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY   
    WHERE MONTH(START_DATE) between 8 and 10
    GROUP BY CAR_ID 
    HAVING COUNT(HISTORY_ID)>=5
    )
AND MONTH(START_DATE) between 8 and 10
GROUP BY MONTH, CAR_ID
HAVING RECORDS>0
ORDER BY MONTH,CAR_ID DESC

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

/* 틀린 답 
SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
GROUP BY CATEGORY 
HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC
*/
SELECT CATEGORY, PRICE AS MAX_PRICE ,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY,PRICE) IN (
    SELECT CATEGORY,MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY 
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
    )
ORDER BY MAX_PRICE DESC

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

SELECT ANIMAL_TYPE,count(*)
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE

Q.동명 동물 수 찾기

SELECT NAME, COUNT(*)
FROM ANIMAL_INS 
GROUP BY NAME 
HAVING COUNT(NAME)>=2
ORDER BY NAME

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

SELECT YEAR(os.SALES_DATE) as YEAR
    ,MONTH(os.SALES_DATE) as MONTH
    ,ui.GENDER as GENDER
    ,COUNT(distinct ui.USER_ID) as USERS 
FROM ONLINE_SALE as os 
INNER JOIN USER_INFO as ui 
ON os.USER_ID = ui.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR , MONTH, GENDER
ORDER BY YEAR , MONTH, GENDER

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

SELECT HOUR(DATETIME) AS HOUR , COUNT(*) AS COUNT
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) between 9 and 19 
GROUP BY HOUR
ORDER BY HOUR

Q.입양 시각 구하기(2)#***

/*
틀린 답
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME) 
ORDER BY HOUR
*/

/*
SET @변수명 = 대입값; 
SELECT @변수명 := 대입값; # :=는 할당 연산자
*/

SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(HOUR(DATETIME)) 
    FROM ANIMAL_OUTS 
    WHERE HOUR(DATETIME)=@HOUR) AS COUNT 
    FROM ANIMAL_OUTS
WHERE @HOUR < 23;

Q.가격대 별 상품 개수 구하기 #***

SELECT 
    TRUNCATE(PRICE,-4) as PRICE_GROUP #4자릿수를 버림. 
    , COUNT(*) as PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
/*
TRUNCATE(PRICE,-4)  #4자릿수를 버림. 
TRUNC(수, [, 자릿수])
수를 지정된 소수점 자리까지 버림하여 반환해주는 함수이다. 
자릿수를 명시하지 않았을 경우 기본값은 0 . 즉, 정수로 반환
*/