SELECT (
    CASE type
        WHEN 0 THEN 'heroes'
        WHEN 1 THEN 'villains'
        ELSE 'neither'
    END
) AS MostFrequent
FROM (
    SELECT type
    FROM mutants
    GROUP BY type
    ORDER BY count(*) DESC
) WHERE rownum <= 1

WITH
RECOUNTS AS
(
  SELECT
    NVL(COUNT(CASE TYPE WHEN 0 THEN TYPE END), 0) AS HEROES,
    NVL(COUNT(CASE TYPE WHEN 1 THEN TYPE END), 0) AS VILLAINS
  FROM MUTANTS
)
SELECT
  CASE WHEN HEROES > VILLAINS THEN 'heroes'
       WHEN HEROES < VILLAINS THEN 'villains'
       ELSE 'neither'
  END AS MOSTFREQUENT
FROM RECOUNTS

select
   case when heroes > villains then 'heroes'
        when heroes < villains then 'villains'
        else 'neither'
   end as MostFrequent
from ( select
        (select count(*) from mutants where type = 0) as heroes,
        (select count(*) from mutants where type = 1) as villains
      from dual)
;