Transact-SQL ( Select ) : 회원데이터, 구매데이터 사용예 #2

--============================================================--
-- SELECT + GROUP BY, HAVING 절
--============================================================--
use sqlDB
go

-- 그룹으로 묶어 주는 역활을 한다.
-- 구매 테이블에서 사용자가 구매한 물품의 개수를 보기 위한 쿼리
select userid, amount from buyTbl order by userid
/* 결과
userid   amount
-------- ------
AJH      10
CJC      5
CJC      5
CJC      2
CJC      2
LCS      1
PJY      3
PJY      2
PJY      1
SKH      5
SKH      1
SKH      1
*/

-- SUM() 함수를 사용해서 구매 개수를 합치고 사용자를 하나로 묶어 주는 절
select userid, sum(amount) from buyTbl group by userid
/* 결과
userid  
-------- -----------
AJH      10
CJC      14
LCS      1
PJY      6
SKH      7
*/

-- 별칭을 붙여서 컬럼 이름을 정하는 쿼리
select userid as [사용자 아이디], sum(amount) as [총 구매 개수]
 from buyTbl group by userid
/* 결과
사용자 아이디  총 구매 개수
-------- -----------
AJH      10
CJC      14
LCS      1
PJY      6
SKH      7
*/

-- 구매액의 총합을 출력해 보자. 구매액은 가격 * 개수이므로,
-- 총합은 sum()을 사용하는 쿼리
select userid as [사용자 아이디], sum(price*amount) as [총 구매 액]
 from buyTbl group by userid
/* 결과
사용자 아이디  총 구매 액
-------- -----------
AJH      150
CJC      2120
LCS      200
PJY      1210
SKH      140
*/

--============================================================--
-- SELECT + 집계 함수
--============================================================--
/*
  -- AVG() : 평균을 구한다.
  -- MIN() : 최소값을 구한다
  -- MAX() : 최대값을 구한다.
  -- COUNT() : 행의 개수를 센다.
  -- COUNT_BIG() : 행의 개수를 센다. 단 결과값이 bigint 형이다.
  -- STDEV() : 표준편차를 구한다.
*/
-- 전체 구매자가 구매한 물품의 개수(AMOUNT)의 평균을 구해보자.
select avg(amount) as [평균구매개수] from buyTbl
/* 결과
평균구매개수
-----------
3
*/

-- CAST(), CONVERT() 함수 사용해서 실수로 변환
select avg(amount * 1.0) as [평균구매개수] from buyTbl
/* 결과
평균구매개수
---------------------------------------
3.166666
*/

-- 사용자별 평균 구매 개수를 구해보도록 하자.
-- group by 를 사용한다.
select userid, avg(amount * 1.0) as [평균구매개수] from buyTbl
 group by userid
/* 결과
userid   평균구매개수
-------- ---------------------------------------
AJH      10.000000
CJC      3.500000
LCS      1.000000
PJY      2.000000
SKH      2.333333
*/

-- 가장 큰 키와 가장 작은 키의 값을 출력하는 쿼리문
select name, max(height), min(height) from userTbl
/* 결과
메시지 8120, 수준 16, 상태 1, 줄 1
열 'userTbl.name'이(가) 집계 함수나 GROUP BY 절에 없으므로
SELECT 목록에서 사용할 수 없습니다.
*/

-- group by 없이는 별도의 열을 집계 함수와 같이 사용할 수 없다는 메시지
select name, max(height), min(height) from userTbl group by name
/* 결과
name             
---------- ------ ------
김남일        183    183
박주영        178    178
박지성        181    181
설기현        178    178
송종국        185    185
안정환        182    182
이영표        181    181
이천수        179    179
조재진        179    179
최진철        142    142
*/

-- 마지막 원하는 결과값
select name, height
from userTbl
where height = (select max(height) from userTbl)
   or height = (select min(height) from userTbl)
/* 결과
name       height
---------- ------
최진철        142
송종국        185
*/

--============================================================--
-- SELECT + HAVING
--============================================================--
-- 우선 이 쿼리 부터..
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
group by userid
/* 결과
사용자      총구매액
-------- -----------
AJH      150
CJC      2120
LCS      200
PJY      1210
SKH      140
*/

-- 이중에서 1000 이상인 사용자만 출력하고 싶을때 조건을 추가
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
where sum(price * amount) > 1000
group by userid
/* 결과
메시지 147, 수준 15, 상태 1, 줄 1
집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면
WHERE 절에 나타날 수 없습니다. 또한 집계 중인 열은 외부 참조입니다.
>> 오류 메시지를 보면 집계 함수는 where 절에 나타날 수 없다는 예기이다.
   이럴때 having를 사용한다.
*/

-- where error -> having 으로 교체
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
group by userid
having sum(price * amount) > 1000
/* 결과
사용자      총구매액
-------- -----------
CJC      2120
PJY      1210
*/

-- 추가로 총구매액이 적은 것부터 나타낸다.
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
group by userid
having sum(price * amount) > 1000
order by sum(price * amount) asc
/* 결과
사용자      총구매액
-------- -----------
PJY      1210
CJC      2120
*/