합산 SUB TOTAL을 좀 구해볼려고 ROLLUP이란걸 보고 있습니다.
WITH T AS
(
SELECT '1-1' AS CT,'서부' AS 지역,'A' AS 등급,'1C' AS 클래스,'38' AS 서브코드,'AMD' AS CPU,'5*5*6' AS 공간,'Prod' AS 구매형태,'PC' AS 종류,'SN12345' AS 그룹SN,'30' AS 수량,'3.58' AS 단가,'0.58' AS 부가세,'M197-34227' AS 입고코드,'DANAWA' AS 대행사,'0.373' AS 이자 FROM DUAL UNION ALL
SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL
SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL
SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL
SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL
SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL
)
SELECT * FROM T
이런 데이터가 있습니다.
1-1 | 서부 | A | 1C | 38 | AMD | 5*5*6 | Prod | PC | SN12345 | 30 | 3.58 | 0.58 | M197-34227 | DANAWA | 0.373 |
1-1 | 서부 | A | 1C | 38 | AMD | 5*5*6 | Prod | PC | SN12345 | 34 | 0.57 | 1.1 | M197-34269 | DANAWA | 0.125 |
1-1 | 서부 | A | 1C | 38 | AMD | 5*5*6 | Prod | PC | SN12345 | 32 | 0.56 | 2.03 | M197-34270 | DANAWA | 0.553 |
1-2 | 동부 | B | 2C | 14 | INTEL | 5*5*6 | Prod | PC | SN67890 | 29 | 0.52 | 0.54 | M198-33535 | DANAWA | 0.124 |
1-2 | 동부 | B | 2C | 14 | INTEL | 5*5*6 | Prod | PC | SN67890 | 18 | 0.45 | 0.52 | M198-32509 | DANAWA | 0.334 |
1-2 | 동부 | B | 2C | 14 | INTEL | 5*5*6 | Prod | PC | SN67890 | 13 | 0.51 | 0.49 | M198-33533 | DANAWA |
0.441 |
이런 데이터 입니다
그런데 저 중간중간 행에 첫번째 열에 대해서 합산값을 표시해야 됩니다. 수량, 단가, 부가세, 이자 만 밑에 줄에 나와야 됩니다.
1-1 | 서부 | A | 1C | 38 | AMD | 5*5*6 | Prod | PC | SN12345 | 30 | 3.58 | 0.58 | M197-34227 | DANAWA | 0.373 |
1-1 | 서부 | A | 1C | 38 | AMD | 5*5*6 | Prod | PC | SN12345 | 34 | 0.57 | 1.1 | M197-34269 | DANAWA | 0.125 |
1-1 | 서부 | A | 1C | 38 | AMD | 5*5*6 | Prod | PC | SN12345 | 32 | 0.56 | 2.03 | M197-34270 | DANAWA | 0.553 |
96 | 4.71 | 3.71 | 1.05 | ||||||||||||
1-2 | 동부 | B | 2C | 14 | INTEL | 5*5*6 | Prod | PC | SN67890 | 29 | 0.52 | 0.54 | M198-33535 | DANAWA | 0.124 |
1-2 | 동부 | B | 2C | 14 | INTEL | 5*5*6 | Prod | PC | SN67890 | 18 | 0.45 | 0.52 | M198-32509 | DANAWA | 0.334 |
1-2 | 동부 | B | 2C | 14 | INTEL | 5*5*6 | Prod | PC | SN67890 | 13 | 0.51 | 0.49 | M198-33533 | DANAWA | 0.441 |
60 | 1.48 | 1.55 | 0.899 |
WITH T AS
(
SELECT '1-1' AS CT,'서부' AS REGION,'A' AS GRADE,'1C' AS CLAS,'38' AS SUBCD,'AMD' AS CPU,'5*5*6' AS AREA,'Prod' AS BUYTYPE,'PC' AS TYPE,'SN12345' AS GRPSN,'30' AS QTY,'3.58' AS PRICE,'0.58' AS ADDER,'M197-34227' AS INCD,'DANAWA' AS SITE,'0.373' AS COST FROM DUAL UNION ALL
SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL
SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL
SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL
SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL
SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL
)
SELECT
CT
, REGION
, GRADE
, CLAS
, SUBCD
, CPU
, AREA
, BUYTYPE
, TYPE
, GRPSN
, QTY
, PRICE
, ADDER
, INCD
, SITE
, COST
FROM T
GROUP BY
ROLLUP(~~~~~)
이 뒤를 아무리 해봐도 잘 모르겠습니다.
도와주세요..
예전에 마농님이 rollup을 아주 쉽게 설명해주셨던 자료가 있었는데
어디 놔뒀는지 모르겠네요.
요약하자면 rollup은 나열된 컬럼을 오른쪽에서 부터 하나씩 제거하면서 group by 한다고 생각하시면 됩니다.
with t(gb1, gb2, gb3, val) as
(
select 'A', 'B', 'C', 1 from dual union all
select 'A', 'B', 'C', 2 from dual union all
select 'A', 'B', 'C', 3 from dual
)
select gb1, gb2, gb3, sum(val)
from t
group by rollup(gb1, gb2, gb3)
-> gb1, gb2, gb3 으로 group by 한 것
-> gb1, gb2로 group by 한 것
-> gb1로 group by 한 것
-> 전체 group by 한 것
만약
group by rollup((gb1, gb2), gb3) 로 그룹바이 하면
-> gb1, gb2, gb3으로 group by 한 것
-> gb1, gb2으로 group by 한 것
-> 전체 group by 한 것
이런 개념으로 생각하시면 이해가 쉬우실듯하고요.
질문하신 것은 같은 원리로
SELECT CT, 지역, 등급, 클래스, 서브코드, CPU, 공간, 구매형태, 종류, 그룹SN, SUM(수량), SUM(단가), SUM(부가세), 입고코드, 대행사, SUM(이자)
FROM T
GROUP BY CT, ROLLUP((지역, 등급, 클래스, 서브코드, CPU, 공간, 구매형태, 종류, 그룹SN, 입고코드, 대행사))
요렇게 하시면 되겠네요.
전체 group by 는 필요 없고 ct별 sum만 필요하니 위와 같이 해주시면 되겠습니다.
WITH T AS ( SELECT '1-1' AS CT,'서부' AS 지역,'A' AS 등급,'1C' AS 클래스,'38' AS 서브코드,'AMD' AS CPU,'5*5*6' AS 공간,'Prod' AS 구매형태,'PC' AS 종류,'SN12345' AS 그룹SN,'30' AS 수량,'3.58' AS 단가,'0.58' AS 부가세,'M197-34227' AS 입고코드,'DANAWA' AS 대행사,'0.373' AS 이자 FROM DUAL UNION ALL SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL ) SELECT CT,지역,등급,클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,SUM(수량),SUM(단가),SUM(부가세),입고코드,대행사,SUM(이자) FROM T GROUP BY (CT,지역,등급),ROLLUP((클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,입고코드,대행사,수량,단가,부가세,이자))
WITH T AS ( SELECT '1-1' AS CT,'서부' AS 지역,'A' AS 등급,'1C' AS 클래스,'38' AS 서브코드,'AMD' AS CPU,'5*5*6' AS 공간,'Prod' AS 구매형태,'PC' AS 종류,'SN12345' AS 그룹SN,'30' AS 수량,'3.58' AS 단가,'0.58' AS 부가세,'M197-34227' AS 입고코드,'DANAWA' AS 대행사,'0.373' AS 이자 FROM DUAL UNION ALL SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','34','0.57','1.1','M197-34269','DANAWA','0.125' FROM DUAL UNION ALL SELECT '1-1','서부','A','1C','38','AMD','5*5*6','Prod','PC','SN12345','32','0.56','2.03','M197-34270','DANAWA','0.553' FROM DUAL UNION ALL SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','29','0.52','0.54','M198-33535','DANAWA','0.124' FROM DUAL UNION ALL SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','18','0.45','0.52','M198-32509','DANAWA','0.334' FROM DUAL UNION ALL SELECT '1-2','동부','B','2C','14','INTEL','5*5*6','Prod','PC','SN67890','13','0.51','0.49','M198-33533','DANAWA','0.441' FROM DUAL ) SELECT CT,지역,등급,클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,SUM(수량),SUM(단가),SUM(부가세),입고코드,대행사,SUM(이자) FROM T GROUP BY CT,지역,등급,클래스,서브코드,CPU,공간,구매형태,종류,그룹SN,대행사, ROLLUP(입고코드) ;