inline view: What percentage of these items exist in each bin selected : Multi column subquery « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. Multi column subquery

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>