Mega Code Archive
Select from another select statement
<![CDATA[
SQL> create table t
2 (item number,
3 bin number,
4 primary key (bin , item) );
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> insert into t values(45,35667);
1 row created.
SQL> insert into t values(45,36767);
1 row created.
SQL>
SQL> insert into t values(46,36767);
1 row created.
SQL> insert into t values(66,36767);
1 row created.
SQL> insert into t values(86,36767);
1 row created.
SQL> insert into t values(96,36767);
1 row created.
SQL>
SQL> select * from t
2 order by 1,2;
ITEM BIN
---------- ----------
2 34353
45 34353
45 35667
45 36767
46 36767
66 36767
76 34353
76 35667
86 36767
89 35667
96 36767
11 rows selected.
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 /
BIN COUNT(*) COUNT(*)/CNT
---------- ---------- ------------
35667 3 .75
36767 1 .25
34353 3 .75
SQL>
SQL> drop table t;
Table dropped.
SQL>
]]>