MSSQL사용, Group by : 집계함수

 

SELECT select_list [ INTO new_table ] [ FROM table_source ]

[ WHERE search_condition ]

[ GROUP BY group_by_expression ] [ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

---

↓지금까지 배운 select문 형식

select 필드명 as '필드명1',.....,(select .....) INTO New_테이블명

from 테이블명(인스턴스명.DB명.스키마명.테이블명)

where 조건

order by 필드명1,필드명2

 

 

 

 

※아래 배울 내용들 참고를 위한 테이블자료

 

 

 

 

-- 구매 테이블에서 각 사용자가 구매한 물품의 개수를 출력

select userid, amount from buytbl;

select userid, amount from buytbl order by userid; select userid, sum(amount) from buytbl order by userid; select sum(amount) from buytbl;

 

 

 

 

 

-- GROUP BY : 그룹으로 묶어 주는 역할 - 집계함수 /* SUM ( ) 합을 구한다. AVG ( ) 평균을 구한다. MIN ( ) 최소값을 구한다. MAX ( ) 최대값을 구한다. COUNT ( ) 행의 개수를 센다. */

 

※Group by를 사용하지 않으면 첫 번째 결과처럼 중복된 이름이 나오는데, Group by를 이용하면 하나의 이름으로 통합되어 결과가 나온다.

 

 

 

 

 

-- 사용자 별로(묶어서) 물건을 몇 개 구매 했는가?

select userid, amount from buytbl order by userid;

 

 

 

-- 사용자 별 구매한 개수의 합 : 집계 함수 sum(), GROUY BY

select userid, sum(amount) from buytbl GROUP BY userid;

select userid '아이디', sum(amount) as '총구매개수' from buytbl GROUP BY userid;

↑두 개의 결과는 같다.

 

 

 

-- 각 사용자별 총 구매 가격은? : 총 구매액 = 가격 * 수량

select userid '아이디', sum(price*amount) '총구매액' from buytbl group by userid;

select userid '아이디', sum(price*amount) '총구매액' from buytbl 

group by userid order by sum(price*amount) desc;

↑두 개의 결과는 같다.

 

 

 

-- 전체 구매자가 1회당 구매한 물품 개수의 평균 =  sum(amount) / count(num)

-- 전체 총 구매 수 sum(amount) / 구매가 발생한 회수 count(num)

select sum(amount)/count(num) as '평균구매개수' from buytbl;

 

 

 

/* 실제 평균값은 2.9166 ..... 개인데 2가 출력 된 이유는 amount의 데이터 형이 int 이기 때문에 결과도 int이다.

형변환 함수인 cast(), convert() 함수, 정수 * 실수 = 실수 , amount *1.0 */ SELECT AVG(amount*1.0) AS [평균구매개수] FROM buyTbl;

SELECT AVG(CAST(amount AS DECIMAL(10,6))) AS [평균구매개수] FROM buyTbl;

↑두 개의 결과는 같다.

 

 

 

-- 각 사용자별로 1회에 물건을 몇 개 구매 했는지 평균.

select userid, AVG(amount*1.0) as '평균구매개수'  from buytbl group by userid;

 

 

 

---

 <연습문제>    -- SUM(), AVG(), MIN(), MAX(), COUNT() : 대표적인 집계 함수

 -- 가장 키가 큰 회원과 가장 키가 작은 회원의 이름과 키를 출력

 

 

 

-- 가장 나이 많은 형(맏형), 가장 어린 동생(막내)

 

 

 

-- 물건 가격이 가장 비싼 물건 3개 출력

 

 

 

-- 서적을 구매한 사람은 몇명? (출력 이름 '몇명')

 

 

 

-- 휴대폰이 있는 사람 누구? (이름 , 국번)

 

 

 

-- 휴대폰이 있는 사용자 수 (휴대폰이있는사용자수)

 

 

 

 

-- 전화번호(국번) 종류에 대한 사용자 수

 

 

 

-- 전화번호(국번) 종류에 대한 사용자 수 null 제외

 

 

 

-- SUM() 함수와 COUNT() 함수를 사용해서 사용자들의 총 구매액과, 평균 구매액을 구하시오!

-- 총 구매액 = sum(price*amount) / 구매건수 count(amount) = 평균구매액

 

 

 

 

 

---

-- 총 구매액이 1000 이상인 사람은? (총 구매액이 1000 이상인 사람을 출력)

select userid '사용자', sum(price*amount) ' 총구매액' from buytbl 

where sum(price*amount) > 1000 group by userid

order by sum(price*amount) desc;

 

[결과]

메시지 147, 수준 15, 상태 1, 줄 110

집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면 WHERE 절에 나타날 수 없습니다. 또한 집계 중인 열은 외부 참조입니다.

↑위 오류를 해결하기 위한 방법은 아래의 Having절을 사용

 

-- Having 절 : 집계 함수에 대해서 검색 조건을 주고 제한하는것

-- 1000이상 구매한 사람 - 총구매액이 많은 사용자 부터

select userid '사용자', sum(price*amount) '총구매액' from buytbl

group by userid having sum(price*amount) > 1000 order by sum(price*amount) desc;

 

 

 

--1 사용자 ID예 'B'가 포함 되는 사용자 중 총 구매액이 100초과,(구매액이 많은 순서로)

 

 

 

 

 

--2 지역 별 평균키를 구하시오(키큰 지역 순으로 출력)

 

 

 

 

 

--3 평균 키가 175보다 작은(미만) 지역은? (키큰 지역 순으로)

 

 

 

 

 

--4 제품그룹별 총 구매액

↓두 번째는 null을 제거한 문장

 

 

 

 

 

--5 제품 그룹별 평균 구매액

 

 

 

 

 

--6 평균 구매액이 100원 미만인 제품그룹은 매장에서 철수 시키려고 하는데 그 대상은?('매장에서 뺄 제품 그룹')

 

 

 

 

 

GROUP BY의 옵션

-- 분류(groupName)별로 소합 , 총합 - rollup 

 

 

 

 

 

-- GROUPING_ID() : 데이터인지, 합계 인지 체크 함수.

-- 결과가 0이면 데이터, 결과가 1이면 합계를 위해 추가 된 열

 

↑맨 아래의 NULL이 추가행이다.

 

 

 

 

 

select groupname, SUM(price * amount) '비용' from buytbl group by groupname;

 

select groupname , sum(price * amount) '비용' from buytbl group by rollup(groupname);

 

select groupname, sum(price * amount) '비용', grouping_id(groupname) '추가행 여부'

from buytbl group by rollup(groupname);

 

select num, groupname, sum(price * amount) '비용' from buytbl group by rollup(groupname,num);

 

select num, groupname, sum(price * amount) '비용', grouping_id(groupname,num) '추가행여부'

from buytbl group by rollup (groupname,num);

 

 

 

 

 

 

 

 

 

 

 

-- CUBE 함수 : ROLLUP과 비슷한 개념이지만 다차원 정보의 데이터를 요약하는데 더 적합한 함수!

CREATE TABLE cubeTbl(prodName NCHAR(3), color NCHAR(2), amount INT);

INSERT INTO cubeTbl VALUES(N'컴퓨터', N'검정', 11);

INSERT INTO cubeTbl VALUES(N'컴퓨터', N'파랑', 22);

INSERT INTO cubeTbl VALUES(N'모니터', N'검정', 33);

INSERT INTO cubeTbl VALUES(N'모니터', N'파랑', 44);

select * from cubeTbl;

 

 

 

-- 물품 별 합

select prodname, sum(amount) '수량합계' from cubeTbl group by prodname;

 

 

select prodname, sum(amount) '수량합계' from cubeTbl group by rollup(prodname);

 

 

-- 색상 별 합

select color, sum(amount) '수량합계' from cubetbl group by color;

 

 

select color, sum(amount) '수량합계' from cubetbl group by rollup(color);

 

 

-- 물품 별 소합계 및 색상 별 소합계 + 총합계 모두 보고 싶은 경우 : cube

select prodname, color, sum(amount) '수량합계' from cubetbl group by cube(color,prodname);