[오라클함수] GROUP BY 컬럼..컬럼 사용하기

오늘은오라클함수중에 GROUP BY ~ 함수에포스팅을해보겠어요

 

1. 테스트를위해 WITH문을사용하여 TDATA 라는데이터를만들어서조회한결과입니다.  

이데이터를이용하여 GROUP BY절을 사용한 쿼리를 만들어 보도록 하겠습니다.

WITH TDATA AS ( SELECT 3 AS 판매량, '미국' 국적, '핸드폰' AS 판매제품 FROM DUAL UNION ALL SELECT 15 AS 판매량, '한국' 국적, '냉장고' AS 판매제품 FROM DUAL UNION ALL SELECT 8 AS 판매량, '대만' 국적, '핸드폰' AS 판매제품 FROM DUAL UNION ALL SELECT 5 AS 판매량, '한국' 국적, '자동차' AS 판매제품 FROM DUAL UNION ALL SELECT 12 AS 판매량, '대만' 국적, '냉장고' AS 판매제품 FROM DUAL UNION ALL SELECT 21 AS 판매량, '한국' 국적, '냉장고' AS 판매제품 FROM DUAL UNION ALL SELECT 42 AS 판매량, '대만' 국적, '냉장고' AS 판매제품 FROM DUAL UNION ALL SELECT 32 AS 판매량, '한국' 국적, '핸드폰' AS 판매제품 FROM DUAL ) SELECT * FROM TDATA

2. GROUP BY 국적,판매제품     

국적, 판매제품별로 판매건수, 판매량합계를 구합니다. 

SELECT    국적   , 판매제품   , COUNT(*) AS 판매건수   , SUM(판매량) AS 판매량합계 FROM TDATA GROUP BY 국적,판매제품;

3. GROUP BY 국적   

국적별로  판매건수, 판매량합계를 구합니다.

SELECT 국적 , COUNT(*) AS 판매건수 , SUM(판매량) AS 판매량합계 FROM TDATA GROUP BY 국적;

4. GROUP BY 판매제품

SELECT 판매제품 , COUNT(*) AS 판매건수 , SUM(판매량) AS 판매량합계 FROM TDATA GROUP BY 판매제품;

4. GROUP BY ROLLUP(국적,판매제품)

SELECT 국적 , 판매제품 , COUNT(*) AS 판매건수 , SUM(판매량) AS 판매량합계 FROM TDATA GROUP BY ROLLUP(국적,판매제품);