SQL Server 2014: 실습10 GROUP BY, HAVING 집계 함수 등

뇌를 자극하는 SQL Server 2012 기본편 217페이지

–GROUP BY–

그룹으로 묶어주는 역할

USE sqlDB;
SELECT userID, amount FROM buyTbl ORDER BY userID;

사용자별로 여러 번의 물건 구매가 이루어져 합계를 보고 싶어졌다.

SELECT userID, sum(amount) FROM buyTbl GROUP BY userID;

구매한 개수를 SUM()함수로 합친 뒤 출력. 집계 함수는 주로 GROUP BY와 함께 사용됨.

SELECT userID AS [사용자 아이디], sum(amount) AS [총 구매 개수] FROM buyTbl GROUP BY userID;

별칭을 사용해 보기 좋게 만들기

SELECT userID AS [사용자 아이디], SUM(price*amount) AS [총 구매액] FROM buyTbl GROUP BY userID;

가격*수량으로 총 구매액 계산함.

–집계 함수–

SELECT AVG(amount) AS [평균구매개수] FROM buyTbl;

전체 구매자가 구매한 물품의 개수의 평균. 사실은 2.9166개인데 int값으로 나옴

SELECT AVG(amount*1.0) AS [평균구매개수] FROM buyTbl;

1.0을 곱하거나…

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

CAST() 함수로 제대로 보여줄 수도 있음.

SELECT userID, AVG(amount*1.0) AS [평균구매개수] FROM buyTbl GROUP BY userID;

사용자별로 물건을 평균 몇 번 구매했는지 구함

SELECT name, height FROM userTbl
WHERE height = (SELECT MAX(height) FROM userTbl)
OR height = (SELECT MIN(height) FROM userTbl);

키가 가장 작은 사람과 큰 사람을 출력

SELECT COUNT(*) FROM userTbl;

전체 회원 10명 출력

SELECT COUNT(mobile1) AS [휴대폰이 있는 사용자] FROM userTbl;

휴대폰이 있는 사용자만 카운트

메시지 탭을 열어보면 Null 값이 제거되었다고 알려줌.

–COUNT()와 SELECT의 결과 비교–

위와 같이 SQL Server Profiler 실행

USE AdventureWorks;
GO
SELECT * FROM Sales.Customer;
GO
SELECT COUNT(*) FROM Sales.Customer;
GO

위 구문 실행.

COUNT(*)과 SELECT *의 Duration 차이가 크다.

–Having절–

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl GROUP BY userID;

사용자별 총 구매액

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl WHERE SUM(price*amount) > 1000 GROUP BY userID;

총 구매액이 1000이상인 사용자만 찾으려고 하니… 집계 함수가 WHERE 절에 나타날 수가 없다고 한다.

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl GROUP BY userID HAVING SUM(price*amount) > 1000;

HAVING 절은 GROUP BY 다음에 나와야 한다.

SELECT userID AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl GROUP BY userID HAVING SUM(price*amount) > 1000
ORDER BY SUM(price*amount);

총구매액이 적은 사용자부터 출력하기 위해 ORDER BY 절 사용

–ROLLUP절–

SELECT num, groupName, SUM(price*amount) AS [비용]FROM buyTbl GROUP BY ROLLUP (groupName, num);

총합 또는 중간 합계가 필요하다면… ROLLUP을 사용.(맨 아래에는 총합계가 나옴)

SELECT groupName, SUM(price*amount) AS [비용]FROM buyTbl GROUP BY ROLLUP (groupName);

소합계 및 총합계만 필요하면 name을 빼면 됨.

SELECT groupName, SUM(price*amount) AS [비용],
GROUPING_ID(groupName) AS [추가행여부]FROM buyTbl GROUP BY ROLLUP (groupName);

GROUPING_ID가 1이면 합계를 위해 추가된 열이라고 보면 됨.

–CUBE 함수–

위와 같은 테이블을 만듦.

SELECT prodName, color, SUM(amount) AS [수량합계]FROM CUBETEST1
GROUP BY CUBE (color, prodName);
GROUP BY CUBE를 사용해 물품별, 색상별 소합계를 모두 볼 수 잇음.

77은 모니터 소합계, 33은 컴퓨터 소합계, 110은 총합계, 검정과 파랑의 44, 66은 색상별 소합계가 출력됨.