|
| Country |
Project |
type |
Total |
14-Sep |
14-Oct |
14-Nov |
| country_a |
project_a |
a |
3 |
1 |
1 |
1 |
| country_a |
project_a |
Sub-Total |
3 |
1 |
1 |
1 |
| country_a |
project_b |
a |
27 |
9 |
9 |
9 |
| country_a |
project_b |
Sub-Total |
27 |
9 |
9 |
9 |
| country_a |
Sub-Total |
a |
30 |
10 |
10 |
10 |
| country_a |
Sub-Total |
Sub-Total |
30 |
10 |
10 |
10 |
| country_b |
project_c |
a |
7 |
3 |
2 |
2 |
| country_b |
project_c |
Sub-Total |
7 |
3 |
2 |
2 |
| country_b |
Sub-Total |
a |
7 |
3 |
2 |
2 |
| country_b |
Sub-Total |
Sub-Total |
7 |
3 |
2 |
2 |
| country_c |
project_d |
b |
9 |
3 |
3 |
3 |
| country_c |
project_d |
Sub-Total |
9 |
3 |
3 |
3 |
| country_c |
project_e |
a |
5 |
1 |
2 |
2 |
| country_c |
project_e |
b |
3 |
1 |
1 |
1 |
| country_c |
project_e |
Sub-Total |
8 |
2 |
3 |
3 |
| country_c |
Sub-Total |
a |
5 |
1 |
2 |
2 |
| country_c |
Sub-Total |
b |
12 |
4 |
4 |
4 |
| country_c |
Sub-Total |
Sub-Total |
17 |
5 |
6 |
6 |
| country_d |
project_f |
a |
6 |
2 |
2 |
2 |
| country_d |
project_f |
Sub-Total |
6 |
2 |
2 |
2 |
| country_d |
Sub-Total |
a |
6 |
2 |
2 |
2 |
| country_d |
Sub-Total |
Sub-Total |
6 |
2 |
2 |
2 |
| country_e |
project_g |
a |
4 |
2 |
1 |
1 |
| country_e |
project_g |
Sub-Total |
4 |
2 |
1 |
1 |
| country_e |
Sub-Total |
a |
4 |
2 |
1 |
1 |
| country_e |
Sub-Total |
Sub-Total |
4 |
2 |
1 |
1 |
| Total |
Sub-Total |
a |
52 |
18 |
17 |
17 |
| Total |
Sub-Total |
b |
12 |
4 |
4 |
4 |
| Total |
Sub-Total |
Sub-Total |
64 |
22 |
21 |
21 |
상기처럼 데이터가 나옵니다
그런데
queyr는 하기와 같은식으로 했습니다
그런데 이때 상기표에서 보시면 country는 6개가 같죠?, project 는 2개가 초기에 같습니다 이것을 각각의 row에서 알수 있는 방법이 있을까요?
with t1 as (
select 'country_a' as country, 1 country_order, 'project_a' as project, 1 project_order, 'a' type, 1 type_order, 1 as one, 1 as two, 1 as three from dual union all
select 'country_a' as country, 1 country_order, 'project_b' as project, 2 project_order, 'a' type, 1 type_order, 1 as one, 1 as two, 1 as three from dual union all
select 'country_b' as country, 2 country_order, 'project_c' as project, 1 project_order, 'a' type, 1 type_order, 1 as one, 1 as two, 1 as three from dual union all
select 'country_c' as country, 3 country_order, 'project_d' as project, 1 project_order, 'a' type, 1 type_order, 1 as one, 1 as two, 1 as three from dual
)
SELECT NVL(T1.country, 'Total') country
, NVL(T1.project, 'Sub-Total') project
, NVL(T1.type, 'Sub-Total') type
, sum(one) + sum(two) + sum(three) AS TOTALMM
,sum(one) one
,sum(two) two
, sum(three) three
FROM T1
GROUP BY CUBE ( (T1.country_order, T1.country)
, (T1.project_ORDER, T1.project)
, (T1.TYPE_ORDER, T1.type)
)
HAVING GROUPING_ID(T1.country_order, T1.project_order, T1.TYPE_ORDER)
NOT IN (4, 5)
AND sum(one + two + three) > 0
ORDER BY T1.country_order
, T1.project_ORDER
, T1.TYPE_ORDER |
