Skip to content

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

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; 

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



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

  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
having count(*) < (select count(*) from cdb_objects c2 where c2.con_id = 2)
5 People found this is helpful