31. GroupBy

--[0] Tempdb에테스트용테이블생성및데이터입력

Use tempdb

Go

 

--Drop Table Orders

Create Table dbo.Orders

(

       [상품명] VarChar(10),

       [판매개수] Int

)

Go

 

Insert Orders Values('RADIO' ,5)

Insert Orders Values('TV' ,3)

Insert Orders Values('AUDIO' ,1)

Insert Orders Values('DVD' ,7)

Insert Orders Values('RADIO' ,3)

Insert Orders Values('DVD' ,8)

Insert Orders Values('TV' ,10)

 

--[1] 집계함수사용검색

Select * From Orders

Select Count([판매개수]) As [총판매횟수] From Orders    -- 7

Select Sum([판매개수]) As [총판매개수] From Orders      -- 37

Select Avg([판매개수]) As [총판매평균] From Orders      -- 5

Select Max([판매개수]) As [최대판매개수] From Orders     -- 10

Select Min([판매개수]) As [최소판매개수] From Orders -- 1

 

 

--[2] 아래구문을실행시켜보면다음과같은에러가나타난다.

-- 'Orders.상품명' 열이집계함수에없고Group By 절이없으므로

-- Select 목록에서사용할수없습니다.

Select [상품명], Avg([판매개수]) As [총판매평균] From Orders

 

 

--[3] 위와같이집계함수로계산되어지지않고그룹화되어출력시킬필드는반드시

-- Group By절에기록하여야한다.

Select [상품명], Avg([판매개수]) As [총판매평균]

From Orders

Group By [상품명]

 

 

--[4] 만약그룹별총판매평균이5이상인것만출력하고자할때에는

-- Having절에조건을추가하여야한다.

Select [상품명], Sum([판매개수]) As [총판매합계], Avg([판매개수]) As[총판매평균]

From Orders

Group By [상품명]

Having Avg([판매개수]) >= 5 -- 판매개수의평균이5이상인것만검색

 

 

--[5] 퀴즈: 각주문별상품판매량이5이상이고, 그룹별총판매평균이7이상인것만

-- [상품명]에오름차순정렬해서출력하시오.

Select [상품명], Sum([판매개수]) As [판매합계]

From Orders

-- Join (여기서는생략)

-- On (여기서는생략)

Where [판매개수] >= 5           -- 그룹전 조건   

Group By [상품명]

Having Avg([판매개수]) >= 7     -- 그룹화된 데이터 조건

Order By [상품명] Asc