database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 40417 게시물 읽기
No. 40417
rollup을 통한 소계, 총계 가져오기
작성자
자바천재
작성일
2014-03-28 17:26
조회수
7,910

with t1 as

(

select '인도네시아' as nat_name

, 'Banyu' as project_name

, 'Staff' as gubun

, 11 as prev

, 11 as curr

, 0 as diff

, 1 as nat_sortorder

, 1 as project_sortorder

, 1 as gubun_sortorder

from dual

union all

select '인도네시아' as nat_name

, 'Banyu' as project_name

, 'Worker' as gubun

, 11 as prev

, 11 as curr

, 0 as diff

, 1 as nat_sortorder

, 1 as project_sortorder

, 2 as gubun_sortorder

from dual

union all

select '인도네시아' as nat_name

, 'Senoro' as project_name

, 'Staff' as gubun

, 10 as prev

, 10 as curr

, 0 as diff

, 1 as nat_sortorder

, 2 as project_sortorder

, 1 as gubun_sortorder

from dual

union all

select '멕시코' as nat_name

, 'Manzanillo' as project_name

, 'Staff' as gubun

, 2 as prev

, 2 as curr

, 0 as diff

, 2 as nat_sortorder

, 1 as project_sortorder

, 1 as gubun_sortorder

from dual

union all

select '멕시코' as nat_name

, 'Manzanillo' as project_name

, 'Worker' as gubun

, 3 as prev

, 2 as curr

, -1 as diff

, 2 as nat_sortorder

, 1 as project_sortorder

, 2 as gubun_sortorder

from dual

union all

select '멕시코' as nat_name

, 'Norte II' as project_name

, 'Staff' as gubun

, 5 as prev

, 4 as curr

, -1 as diff

, 2 as nat_sortorder

, 2 as project_sortorder

, 1 as gubun_sortorder

from dual

union all

select '멕시코' as nat_name

, 'Norte II' as project_name

, 'Worker' as gubun

, 3 as prev

, 3 as curr

, 0 as diff

, 2 as nat_sortorder

, 1 as project_sortorder

, 2 as gubun_sortorder

from dual

)

select *

from t1

order by nat_sortorder, project_sortorder, gubun_sortorder

 

상기의 데이터로

하기와 같은 모양으로 나와야 합니다

모양은 나오는데 Sorting이 문제네요

nat_sortorder, project_sortorder, gubun_sortorder 이것으로 소팅을 해야하고

그밑에 소계, 맨 끝에 총계가 나와야하는데요.....

구분별로 "Total"

프로젝트명에 각 국가별로 "Sub-Total"이라는 명칭으로

 

NVL("국가", 'Total') "국가"

,NVL2("구분", NVL("프로젝트명", 'Sub-Total'), 'Sub-Total') "프로젝트명

,구분

,SUM(금주) AS 금주

,SUM(전주) AS 전주

,SUM(차이) AS 차이

 

GROUP BY GROUPING SETS ( ROLLUP("국가", "구분", "프로젝트명"), "구분")

 

위에 같은 방법을 써서 얼추 나오긴 하는데... Sorting 에서 막히네요

좋은방법이 있을까요?

 

----------------------------------------------------------------------

국가 프로젝트명 구분 지난주 금주 차이

---------------------------------------------------------------------

인도네시아 Banyu Total 11 11 0

Staff 11 11 0

Senoro Total 10 10 0

Staff 10 10 0

Sub-Total Sub-Total 21 21 0

Staff 21 21 0

Worker 0 0 0

----------------------------------------------------------------------

멕시코 Manzanillo Total 5 4 -1

Staff 2 2 0

Worker 3 2 -1

Norte II Total 8 7 -1

Staff 5 4 -1

Worker 3 3 0

Sub-Total Sub-Total 13 11 -2

Staff 7 6 -1

Worker 6 5 -1

----------------------------------------------------------------------

Total Total 34 32 -2

Staff 28 27 -1

Worker 6 5 -1

----------------------------------------------------------------------

이 글에 대한 댓글이 총 1건 있습니다.

WITH t1 AS
(
SELECT '인도네시아' AS nat_name
     , 'Banyu'      AS project_name
     , 'Staff'      AS gubun
     , 11           AS prev
     , 11           AS curr
     , 0            AS diff
     , 1            AS nat_sortorder
     , 1            AS project_sortorder
     , 1            AS gubun_sortorder
  FROM dual
UNION ALL SELECT '인도네시아', 'Banyu'     , 'Worker', 11, 11,  0, 1, 1, 2 FROM dual
UNION ALL SELECT '인도네시아', 'Senoro'    , 'Staff' , 10, 10,  0, 1, 2, 1 FROM dual
UNION ALL SELECT '멕시코'    , 'Manzanillo', 'Staff' ,  2,  2,  0, 2, 1, 1 FROM dual
UNION ALL SELECT '멕시코'    , 'Manzanillo', 'Worker',  3,  2, -1, 2, 1, 2 FROM dual
UNION ALL SELECT '멕시코'    , 'Norte II'  , 'Staff' ,  5,  4, -1, 2, 2, 1 FROM dual
UNION ALL SELECT '멕시코'    , 'Norte II'  , 'Worker',  3,  3,  0, 2, 2, 2 FROM dual
)
SELECT NVL(nat_name, 'Total') nat_name
     , NVL(project_name, 'Sub-Total') project_name
     , NVL(gubun, 'Sub-Total') gubun
     , SUM(prev) prev
     , SUM(curr) curr
     , SUM(diff) diff
  FROM t1
 GROUP BY CUBE ( (nat_sortorder, nat_name)
               , (project_sortorder, project_name)
               , (gubun_sortorder, gubun)
               )
HAVING GROUPING_ID(nat_sortorder, project_sortorder, gubun_sortorder)
       NOT IN (4, 5)
 ORDER BY nat_sortorder
     , project_sortorder
     , gubun_sortorder NULLS FIRST
;

마농(manon94)님이 2014-03-31 17:35에 작성한 댓글입니다.
이 댓글은 2014-03-31 17:39에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
40421오라클 데이터베이스 대용량 데이터 처리 방식 문의
박래준
2014-04-04
7020
40420구간별 데이터를 일자별로 출력? [1]
sql초보자
2014-04-03
7034
40418XMLAGG 사용시 &로 바뀌는 현상 [5]
XML
2014-04-01
11240
40417rollup을 통한 소계, 총계 가져오기 [1]
자바천재
2014-03-28
7910
40415대용량 데이터 처리질문
김한결
2014-03-26
6636
40414데이터 통합 쿼리질문 [5]
량디
2014-03-25
7096
40413centos 6.5 64bit 오라클9i 는 어디가면 다운받을수 있나요? [1]
염정훈
2014-03-25
6367
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다