Compare SQL Count

I want to return the name, which has not the con_id nr 2 and has more objects (count(*)) than in con_id nr 2.

My statement isnt working because of the count.. can someone help me pls

Select v.name
From CDB_objects c, v$containers v, CDB_objects c2
Where object_type like '%JAVA%'
   And c.con_id = v.con_id
   And owner not in ('MDSYS', 'OJVMSYS')
   And c.con_id != 2
   And c.count(*) < c2.count(*)
   Group by v.name; 

the return statement without the compare looks like this

OWNER                STATUS      CNT NAME
-------------------- -------- ------ ---------------
SYS                  VALID     37337 CDB$ROOT
SYS                  VALID     37451 PDB1
SYS                  VALID     37339 PDB2
SYS                  VALID     37338 PDB3

Answer

WHERE clause is applied before GROUP BY. What you’re looking for is HAVING

Also you don’t have any join condition with c2, and those expressions: c.count(*), c2.count(*) are not syntactically correct.

Anyway I think you need a simple query like this

select v.name
  from cdb_objects c, v$containers v
 where object_type like '%JAVA%'
   and c.con_id = v.con_id
   and owner not in ('MDSYS', 'OJVMSYS')
   and c.con_id != 2
 group by v.name
having count(*) < (select count(*) from cdb_objects c2 where c2.con_id = 2)