GROUING OF TOTAL

I am joining some table to display the data as shown aboveselect COUNTRY,STATE,TIMING,sum(AMOUNT) from table1,table2where table1.country=table2.country group by COUNTRY,STATE,TIMING;COUNTRY STATE TIMING AMOUNT====================================AUSTRULIA REGION1 MORNING 20AUSTRULIA REGION1 EVENING 30AUSTRULIA REGION1 NIGHT 12AUSTRULIA REGION2 MORNING 17AUSTRULIA REGION2 EVENING 13AUSTRULIA REGION2 NIGHT 20actually i want data like thisAUSTRULIA REGION1 MORNING 20AUSTRULIA REGION1 EVENING 30AUSTRULIA REGION1 NIGHT 12AUSTRULIA REGION2 MORNING 17AUSTRULIA REGION2 EVENING 13AUSTRULIA REGION2 NIGHT 20AUSTRULIA TOTAL MORNING 37AUSTRULIA TOTAL EVENING 43AUSTRULIA TOTAL NIGHT 32lets say I don’t have region2 Then also I want to display data likeAUSTRULIA REGION1 MORNING 20AUSTRULIA REGION1 EVENING 30AUSTRULIA REGION1 NIGHT 12AUSTRULIA TOTAL MORNING 20AUSTRULIA TOTAL EVENING 30AUSTRULIA TOTAL NIGHT 12
How to do so?Thanks


-------------------------------------------------------------------------------------------------
http://www.psoug.org/reference/rollup.html

SELECT COUNTRY,
STATE,
TIMING,
SUM(AMOUNT)
FROM table1,
table2
WHERE table1.country = table2.country
GROUP BY COUNTRY,
STATE,
TIMING
UNION
SELECT COUNTRY,
'TOTAL',
TIMING,
SUM(AMOUNT)
FROM table1,
table2
WHERE table1.country = table2.country
GROUP BY COUNTRY,
TIMING;
SCOTT@soti_10> with Test_Data as (
2 select 'AUSTRULIA' as country, 'REGION1' as state, 'MORNING' as timing, 20 as amount from dual union all
3 select 'AUSTRULIA' as country, 'REGION1' as state, 'EVENING' as timing, 30 as amount from dual union all
4 select 'AUSTRULIA' as country, 'REGION1' as state, 'NIGHT' as timing, 12 as amount from dual union all
5 select 'AUSTRULIA' as country, 'REGION2' as state, 'MORNING' as timing, 17 as amount from dual union all
6 select 'AUSTRULIA' as country, 'REGION2' as state, 'EVENING' as timing, 13 as amount from dual union all
7 select 'AUSTRULIA' as country, 'REGION2' as state, 'NIGHT' as timing, 20 as amount from dual
8 )
9 select country,
10 nvl(state, 'TOTAL') as state,
11 timing,
12 sum(amount) as amount
13 from Test_Data
14 group by country, timing, rollup(state)
15 order by state,
16 case when timing = 'MORNING' then 1
17 when timing = 'EVENING' then 2
18 when timing = 'NIGHT' then 3
19 end
20 ;

COUNTRY STATE TIMING AMOUNT
--------- ------- ------- ----------
AUSTRULIA REGION1 MORNING 20
AUSTRULIA REGION1 EVENING 30
AUSTRULIA REGION1 NIGHT 12
AUSTRULIA REGION2 MORNING 17
AUSTRULIA REGION2 EVENING 13
AUSTRULIA REGION2 NIGHT 20
AUSTRULIA TOTAL MORNING 37
AUSTRULIA TOTAL EVENING 43
AUSTRULIA TOTAL NIGHT 32

9 rows selected.