GROUP BY(2)
🍴 즐겨찾기가 가장 많은 식당 정보 출력하기
서브쿼리를 이용, GROUP BY를 사용해 그룹별 FAVORITES가 제일 큰(MAX) FOOD_TYPE을 선택하도록 한다.
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;🔖조건에 맞는 사용자 정보 조회하기
- CONCAT
CONCAT(문자열1, 문자열2 [, 문자열3 ...])
둘 이상의 문자열을 입력한 순서대로 합쳐서 반환해주는 함수 - SUBSTR
SUBSTR(문자열, 시작지점, 길이)
문자열을 시작지점부터 길이만큼 추출 (길이 미입력시 끝까지 추출) - LEFT, MID, RIGHT
LEFT(문자, 가져올 갯수)
MID(문자, 시작 위치, 가져올 갯수)
RIGHT(문자, 가져올 갯수)
SUBSTR과 MID는 똑같이 동작
SELECT USER_ID, NICKNAME,
CONCAT(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) AS '전체주소',
CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8,4)) AS '전화번호'
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(*)>=3)
ORDER BY USER_ID DESC;서브쿼리를 작성해 조건에 맞는 ID 행을 추출하도록 한다.
🚗 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
서브쿼리에 "2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차" 조건을 넣어주면 된다.
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(CAR_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (MONTH(START_DATE) BETWEEN 8 AND 10)
AND 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(*)>=5)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC;🚙 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
CASE WHEN을 이용해 '대여중'과 '대여 가능'으로 크게 나눠준다.- '2022-10-16'이 존재하는 CAR_ID가 하나라도 있으면 '대여중' 조건에 해당하므로 서브 쿼리를 작성해준다. (CAR_ID
IN~)
SELECT CAR_ID, (CASE
WHEN CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE '2022-10-16'
BETWEEN DATE_FORMAT(START_DATE,"%Y-%m-%d")
AND DATE_FORMAT(END_DATE,"%Y-%m-%d")
) THEN '대여중'
ELSE '대여 가능'
END) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;🔎 조건에 맞는 사용자와 총 거래금액 조회하기
테이블에서 "완료"된 중고 거래의 총금액이 "70만원" 이상인 사람을 출력
SELECT A.USER_ID, A.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_USER AS A
JOIN USED_GOODS_BOARD AS B
ON A.USER_ID = B.WRITER_ID
WHERE B.STATUS = "DONE"
GROUP BY B.WRITER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES;🎁 년, 월, 성별 별 상품 구매 회원 수 구하기
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재하기 때문에 꼭 DISTINCT을 작성해줘야 한다.
SELECT YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
A.GENDER AS GENDER,
COUNT(DISTINCT A.USER_ID) AS USERS
FROM USER_INFO AS A
JOIN ONLINE_SALE AS B
ON A.USER_ID = B.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;🍦 주문량이 많은 아이스크림들 조회하기
JOIN을 이용해 테이블을 연결하고, FIRST_HALF의 FLAVOR 기준으로GROUP BY를 이용해 아이스크림 별로 그룹을 만들어 준다.- JULY 테이블에 있는 TOTAL_ORDER는 다 더 해줘야하므로
SUM함수를 이용하면 된다. LIMIT를 이용해 상위 3개만 출력하도록 하면 된다.
SELECT F.FLAVOR
FROM JULY AS J
JOIN FIRST_HALF AS F
ON J.FLAVOR = F.FLAVOR
GROUP BY F.FLAVOR
ORDER BY F.TOTAL_ORDER + SUM(J.TOTAL_ORDER) DESC
LIMIT 3📖 저자 별 카테고리 별 매출액 집계하기
JOIN으로 테이블 3개를 연결해준다.- AUTHOR_ID와 CATEGORY로
GROUP BY
🧨 SUM(S.SALES B.PRICE) 대신 (SUM(S.SALES) B.PRICE) 가 안되는 이유는, 같은작가/Category 의 책이 2권 이상이고 가격이 서로 다른 경우가 있을 수 있기 때문입니다.
SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,
SUM(B.PRICE * C.SALES) AS TOTAL_SALES
FROM BOOK AS B
JOIN AUTHOR AS A
ON A.AUTHOR_ID = B.AUTHOR_ID
JOIN BOOK_SALES AS C
ON B.BOOK_ID = C.BOOK_ID
WHERE DATE_FORMAT(C.SALES_DATE,"%Y-%m")='2022-01'
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID, B.CATEGORY DESC🍣 식품분류별 가장 비싼 식품의 정보 조회하기
CATEGORY별로 PRICE을 묶고 각 그룹에 최대 PRICE만을 계산하여 최대 가격이 해당하는 열을 선택해 출력하도록 한다.
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE)
FROM FOOD_PRODUCT
GROUP BY CATEGORY)
AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY MAX_PRICE DESC
뭐든지 난 열심히 하지