무료 동영상 강의 https://youtu.be/KrGfSWGeauA
GROUP BY에 여러 컬럼(항목)을 정의할 수 있다.
-- [SQL-5-6-1] GROUP BY 처리전 SQL
SELECT T1.ItemId ,T1.ItemNm ,T1.ItemSizeCd ,T1.HotColdCd
FROM startdb.Item T1
WHERE T1.ItemCat != 'COF'
AND T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
ORDER BY T1.ItemSizeCd ,T1.HotColdCd;
-- GROUP BY 처리전 결과
ItemId ItemNm ItemSizeCd HotColdCd
------ ------------------- ---------- ---------
HCHB Hot Chocolate(B) BIG HOT
LEMR Lemonade(R) REG COLD
BMFR Blueberry Muffin(R) REG COLD
CMFR Chocolate Muffin(R) REG COLD
HCHR Hot Chocolate(R) REG HOT
BGLR Bagel(R) REG HOT
-- [SQL-5-6-2] ItemSizeCd, HotColdCd별 GROUP BY
SELECT T1.ItemSizeCd ,T1.HotColdCd ,COUNT(*) ItemCnt
FROM startdb.Item T1
WHERE T1.ItemCat !='COF'
GROUP BY T1.ItemSizeCd ,T1.HotColdCd
ORDER BY T1.ItemSizeCd ,T1.HotColdCd;
-- ItemSizeCd, HotColdCd별 GROUP BY 처리후 결과
ItemSizeCd HotColdCd ItemCnt
---------- --------- -------
BIG HOT 1
REG COLD 3
REG HOT 2
아래는 주문(Ord) 데이터를 주문년월(OrdYm)별 회원ID별 GROUP BY 처리한 SQL이다.
-- [SQL-5-6-3]
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm ,T1.MemberId
,SUM(T1.OrdAmt) SumOrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220401','%Y%m%d')
AND T1.MemberId IN ('M0207','M0208','M0209')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m') ,T1.MemberId
ORDER BY DATE_FORMAT(T1.OrdDtm,'%Y%m') ,SumOrdAmt DESC;
OrdYm MemberId SumOrdAmt
------ -------- ---------
202201 M0207 34000.000
202201 M0209 16000.000
202201 M0208 8000.000
202202 M0209 15000.000
202202 M0207 14000.000
202202 M0208 11000.000
202203 M0209 23500.000
202203 M0207 13500.000
202203 M0208 11000.000
