[SQL_GROUP BY]

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

SELECT A.INGREDIENT_TYPE, SUM(B.TOTAL_ORDER) AS TOTAL_ORDER
FROM ICECREAM_INFO A, FIRST_HALF B
WHERE A.FLAVOR=B.FLAVOR
GROUP BY A.INGREDIENT_TYPE
ORDER BY B.TOTAL_ORDER

📌가격대 별 상품 개수 구하기

SELECT TRUNCATE(PRICE,-4) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

📌입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9
  AND HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR ASC;

#참고
SELECT
    ANIMAL_ID
    ,NAME
    ,DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

📌동명 동물 수 찾기

SELECT NAME,COUNT(NAME) 
FROM ANIMAL_INS 
GROUP BY NAME
HAVING COUNT(NAME)>1
ORDER BY NAME

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

SELECT ANIMAL_TYPE ,
COUNT(ANIMAL_TYPE) AS count 
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

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

SELECT CAR_ID,
CASE WHEN CAR_ID IN (SELECT CAR_ID
                    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) THEN '대여중'
                    ELSE '대여 가능'
                END 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

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

SELECT b.CATEGORY, SUM(bs.SALES) AS TOTAL_SALES
FROM BOOK b
JOIN BOOK_SALES bs
ON b.BOOK_ID = bs.BOOK_ID
WHERE bs.SALES_DATE LIKE '2022-01%'
GROUP BY b.CATEGORY
ORDER BY b.CATEGORY

#또다른 정답 코드
SELECT b.CATEGORY, SUM(bs.SALES) AS TOTAL_SALES
FROM BOOK b, BOOK_SALES bs
WHERE b.BOOK_ID = bs.BOOK_ID
AND bs.SALES_DATE LIKE '2022-01%'
GROUP BY b.CATEGORY
ORDER BY b.CATEGORY

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

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),MCDP_CD

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

SELECT A.INGREDIENT_TYPE, SUM(B.TOTAL_ORDER) AS TOTAL_ORDER
FROM ICECREAM_INFO A, FIRST_HALF B
WHERE A.FLAVOR=B.FLAVOR
GROUP BY A.INGREDIENT_TYPE
ORDER BY B.TOTAL_ORDER

 

[SQL] 프로그래머스 DATETIME에서 DATE로 형변환

 

[SQL] join할때 on 과 where의 차이