exp1 and exp2 Select * exp 1 gives me:

Select * exp 2 gives me:

Now what I want to do is count every id and then print only when number of id's are above average

So in math way it should be something like this exp_2_id 1 = count 2 exp_2_id 2 = count 1 average = 3/2 = 1.5 Should print only exp_2 id 1 cause it is 2

Hope that someone can explain me how to do it

So as an output I want to have only

CodePudding user response：

This is how I understood it; read comments within code.

Sample data:

SQL> with
  2  exp1 (id, exp_2_id, name) as
  3    (select 1, 1, 'a' from dual union all
  4     select 2, 1, 'b' from dual union all
  5     select 3, 2, 'c' from dual
  6    ),
  7  exp2 (id, name) as
  8    (select 1, 'print'    from dual union all
  9     select 2, 'no_print' from dual
 10    ),

Query begins here:

 11  temp as
 12    -- number of rows in each of these tables
 13    (select (select count(*) from exp1) cnt_1,
 14            (select count(*) from exp2) cnt_2
 15     from dual
 16    ),
 17  temp2 as
 18    -- number of rows per each EXP_2_ID in EXP1 table
 19    (select a.exp_2_id,
 20            count(*) cnt_id
 21     from exp1 a
 22     group by a.exp_2_id
 23    )
 24  -- Finally, select EXP2 row(s) whose number (per ID) is larger than the average
 25  select b.id, b.name
 26  from exp2 b join temp2 t2 on t2.exp_2_id = b.id
 27  where t2.cnt_id > (select t.cnt_1 / t.cnt_2 from temp t);

        ID NAME
---------- --------
         1 print

SQL>

CodePudding user response：

An option would be using Count Analytic Function such as

WITH e AS 
(
  SELECT DISTINCT
         e2.ID , 
         e2.Name,
         COUNT(*) OVER () / COUNT(*) OVER (PARTITION BY e2.ID) AS ratio,
         COUNT(DISTINCT e2.ID) OVER () AS total   
    FROM exp2 e2
    JOIN exp1 e1
      ON e1.exp_2_ID = e2.ID
)
SELECT ID, Name
  FROM e
 WHERE total > ratio

Demo

CodePudding user response：

You want to look at table exp1 and count rows per exp_2_id. Then you want to keep those exp_2_id the count of which is above average. At last you want to select all rows of table exp2 that match those IDs.

You can use AVG(COUNT(*)) OVER () to get the average of the counts.

One way to write the query:

select *
from exp2
where (id, 'ABOVE AVERAGE') in
(
  select 
    exp_2_id,
    case when count(*) > avg(count(*)) over () 
      then 'ABOVE AVERAGE' 
      else 'NOT ABOVE AVERAGE'
    end
  from exp1
  group by exp_2_id 
);

If this looks a bit clumsy, it is because window functions are applied last in a query, so we cannot use WHERE directly to get the rows above average. Some DBMS feature a QUALIFY clause that does that, but Oracle does not have such a clause.