Skip to Main Content

Grouping Sets

Sven W.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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Jul 28 2010
Added on Jun 29 2010
7 comments
1,228 views