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?