Grouping Sets

Jun 29 2010 — edited Jun 30 2010
Database Version 10.2.0.4

prelimary information

I'm preparing some statistical data. Here is my simplified example:
``````XXX@yyyy> with testdata as (select 'Europe' site, 1 product, 10 amount from dual union all
2                    select 'Europe' site, 2 product, 20 amount from dual union all
3                    select 'USA' site, 1 product, 30 amount from dual union all
4                    select 'USA' site, 3 product, 40 amount from dual union all
5                    select 'Japan' site, 1 product, 50 amount from dual union all
6                    select 'Japan' site, 2 product, 60 amount from dual )
7  select * from testdata;

SITE      PRODUCT     AMOUNT
------ ---------- ----------
Europe          1         10
Europe          2         20
USA             1         30
USA             3         40
Japan           1         50
Japan           2         60

6 rows selected.

XXX@yyy> ``````
I now want to group on the regions (sites) and count the number of different products there.
Also I have a total distinct count which is important.

This is the current select:
``````-- number of different products on each site
with testdata as (select 'Europe' site, 1 product, 10 amount from dual union all
select 'Europe' site, 2 product, 20 amount from dual union all
select 'Europe' site, 2 product, 25 amount from dual union all
select 'USA' site, 1 product, 30 amount from dual union all
select 'USA' site, 3 product, 40 amount from dual union all
select 'Japan' site, 1 product, 50 amount from dual union all
select 'Japan' site, 2 product, 60 amount from dual )
select decode(grouping_id(site), 0, site, 'all sites') as site,
count(distinct product) "# unique products", sum(amount) sum_amo
from testdata
group by grouping sets ((site), ());

SITE      # unique products    SUM_AMO
--------- ----------------- ----------
Europe                    2         55
Japan                     2        110
USA                       2         70
all sites                 3        235``````
problem

I now want to add a special line "all sites without Japan".

Somehow I can't find a decent way to add this row.

Wanted Output:
``````SITE      # unique products    SUM_AMO
--------- ----------------- ----------
Europe                    2         55
Japan                     2        110
USA                       2         70
all sites w/o Japan       3        125
all sites                 3        235``````
My real select runs over aproximately 6 million records and needs a few minutes to sort this (count distinct is slow). So I'm prefering a solution where I do not access the same table twice (else this would be too easy).

Any ideas?