[TIL] 241122

#가격대 별 상품 개수 구하기
SELECT PRICE-PRICE%10000 AS PRICE_GROUP,
       COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1
;

#상품 별 오프라인 매출 구하기
SELECT P.PRODUCT_CODE,
       SUM(P.PRICE*O.SALES_AMOUNT) SALES 
FROM PRODUCT P JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY 1
ORDER BY 2 DESC, 1
;

#재구매가 일어난 상품과 회원 리스트 구하기
SELECT USER_ID,
       PRODUCT_ID
FROM ONLINE_SALE
GROUP BY 1,2
HAVING COUNT(*)>1
ORDER BY 1,2 DESC
;

#진료과별 총 예약 횟수 출력하기
SELECT MCDP_CD as '진료과 코드',
       count(*) as '5월예약건수'
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = '05'
GROUP BY 1
ORDER BY 2, 1
;

#성분으로 구분한 아이스크림 총 주문량
SELECT I.INGREDIENT_TYPE,
       SUM(F.TOTAL_ORDER) AS TOTAL_ORDER 
FROM ICECREAM_INFO I JOIN FIRST_HALF F ON I.FLAVOR = F.FLAVOR
GROUP BY 1
ORDER BY 2 
;