SQL>
SQL>
SQL>
SQL> create table t (
2 item number,
3 bin number,
4 primary key (bin , item)
5 );
Table created.
SQL>
SQL> insert into t values(2, 34353);
1 row created.
SQL> insert into t values(45,34353);
1 row created.
SQL> insert into t values(76,34353);
1 row created.
SQL> insert into t values(76,35667);
1 row created.
SQL> insert into t values(89,35667);
1 row created.
SQL>
SQL>
SQL> select * from t order by 1,2;
ITEM BIN
---------- ----------
2 34353
45 34353
76 34353
76 35667
89 35667
5 rows selected.
SQL>
SQL> select bin, count(*), count(*)/cnt
2 from ( select bin, count(distinct item) over () AS cnt
3 from t
4 where item in (2,45,76,89) )
5 group by bin, cnt
6 /
BIN COUNT(*) COUNT(*)/CNT
---------- ---------- ------------
34353 3 .75
35667 2 .5
2 rows selected.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>