GROUP BY
진료과별 총 예약 횟수 출력하기
SELECT
MCDP_CD AS '진료과코드',
COUNT(MCDP_CD) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY COUNT(MCDP_CD) ASC, MCDP_CD ASCSELECT MCDP_CD AS '진료과코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY MCDP_CD
ORDER BY COUNT(*), MCDP_CD식품분류별 가장 비싼 식품의 정보 조회하기
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대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
기간 조건은 driven table과 전체 WHERE 절 둘 다 걸어주어야 한다. WHERE 절 내 Driven 테이블에서만 기간 조건 걸어주면서 문제의 원인이 무엇인지 많이 헤맸었다.
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (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)
AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID
HAVING COUNT(*) <> 0
ORDER BY MONTH ASC, CAR_ID DESC카테고리 별 도서 판매량 집계하기
SELECT b.CATEGORY, SUM(s.SALES) AS TOTAL_SALES
FROM BOOK b
JOIN (SELECT BOOK_ID, SUM(SALES) as SALES, SALES_DATE
FROM BOOK_SALES
WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY BOOK_ID) as s
ON b.BOOK_ID = s.BOOK_ID
WHERE s.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY b.CATEGORY
ORDER BY b.CATEGORY즐겨찾기가 가장 많은 식당 정보 출력하기
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
SELECT FOOD_TYPE, MAX(FAVORITES) AS FAV
FROM REST_INFO
GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC자동차 대여 기록에서 대여중/대여 가능 여부 구분하기
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 AVAILABILTY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC성분으로 구분한 아이스크림 총 주문량
SELECT i.INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF f
JOIN ICECREAM_INFO i
ON f.FLAVOR = i.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
SELECT CAR_TYPE, COUNT(CAR_ID) CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%'
OR OPTIONS LIKE '%열선시트%'
OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE저자 별 카테고리 별 매출액 집계하기
SELECT b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY
, SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
FROM BOOK b
JOIN AUTHOR a
ON b.AUTHOR_ID = a.AUTHOR_ID
JOIN BOOK_SALES bs
ON b.BOOK_ID = bs.BOOK_ID
WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC고양이와 개는 몇 마리 있을까
SELECT b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY
, SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
FROM BOOK b
JOIN AUTHOR a
ON b.AUTHOR_ID = a.AUTHOR_ID
JOIN BOOK_SALES bs
ON b.BOOK_ID = bs.BOOK_ID
WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC동명 동물 수 찾기
SELECT NAME, COUNT(NAME) as COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME입양 시각 구하기 (1)
SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME)) as COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) > 8 AND HOUR(DATETIME) < 20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)년, 월, 성별 별 상품 구매 회원 수 구하기
SELECT YEAR(o.SALES_DATE) AS YEAR
, MONTH(o.SALES_DATE) AS MONTH, u.GENDER GENDER
, COUNT(DISTINCT o.USER_ID) USERS
FROM ONLINE_SALE o
JOIN USER_INFO u
ON u.USER_ID = o.USER_ID
WHERE u.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER입양 시각 구하기 (2)
WITH RECURSIVE TIMETABLE(HOUR) AS (
SELECT 0
UNION
SELECT TIMETABLE.HOUR + 1 FROM TIMETABLE WHERE TIMETABLE.HOUR < 23
)
SELECT HOUR, COUNT(A.ANIMAL_ID)
FROM TIMETABLE AS T LEFT JOIN ANIMAL_OUTS AS A ON T.HOUR = HOUR(A.DATETIME)
GROUP BY HOUR
ORDER BY HOURSET @hour := -1;
SELECT (@hour := @hour + 1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) AS COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23가격대 별 상품 개수 구하기
SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUPLast updated