31.MS_SQL 2008 - 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* From Orders Where [판매개수] >= 5 --1차실행

--2차실행(최종결과확인)

Select [상품명], SUM([판매개수]) As [판매합계] --수정/추가

From Orders

Where [판매개수] >= 5

Group By [상품명]

Having SUM([판매개수]) >= 7

Order By [상품명] Asc

---

31GroupBy.sql