|
SQL>
SQL> create table t
2 (item number,
3 bin number,
4 primary key (bin , item) );
Table created.
SQL>
SQL> insert into t values(2, 34);
1 row created.
SQL> insert into t values(45,34);
1 row created.
SQL> insert into t values(76,34);
1 row created.
SQL> insert into t values(76,35);
1 row created.
SQL> insert into t values(89,35);
1 row created.
SQL> insert into t values(45,35);
1 row created.
SQL> insert into t values(45,36);
1 row created.
SQL>
SQL> select * from t
2 order by 1,2;
2 34
45 34
45 35
45 36
76 34
76 35
89 35
7 rows selected.
SQL>
SQL>
SQL> select bin, count(*), count(*)/cnt
2 from (
3 select bin, count(distinct item) over () AS cnt
4 from t
5 where item in (2,45,76,89) )
6 group by bin, cnt
7 /
34 3 .75
35 3 .75
36 1 .25
3 rows selected.
SQL> drop table t;
Table dropped.
SQL>
|