如果这些结果是单字的，并且用空格隔开，那么这样的通用方法可能会让您感兴趣。
第1-8行代表样本数据；你已经有了，所以不用打了。您可能需要的代码从第10行开始。

SQL> with fruit (fruit) as
  2  -- sample data; you have that in a table
  3    (select 'Apples'         from dual union all
  4     select 'Apples Oranges' from dual union all
  5     select 'Apples Oranges' from dual union all
  6     select 'Apples Lemon'   from dual union all
  7     select 'Oranges Plums'  from dual
  8    ),
  9  -- split fruits to rows
 10  temp as
 11    (select regexp_substr(fruit, '[^ ]+', 1, column_value) fruit
 12     from fruit cross join
 13       table(cast(multiset(select level from dual
 14                           connect by level <= regexp_count(fruit, ' ') + 1
 15                          ) as sys.odcinumberlist))
 16    )
 17  select fruit, count(*)
 18  from temp
 19  group by fruit
 20  order by fruit;

FRUIT                                                      COUNT(*)
-------------------------------------------------------- ----------
Apples                                                            4
Lemon                                                             1
Oranges                                                           3
Plums                                                             1

SQL>

不管是sum还是count，它都是有效的
连接到：oracle database 12c enterprise edition release 12.2.0.1.0-64位产品

SQL> with t as
(
select 'Apples'            as fruits from dual union all
select 'Apples Oranges'    as fruits from dual union all
select 'Apples Oranges'    as fruits from dual union all
select 'Apples'            as fruits from dual union all
select 'Oranges'           as fruits  6   from dual
) select
     SUM (CASE WHEN fruits LIKE '%Apples%' THEN '1' END) AS Apples ,
     SUM (CASE WHEN fruits LIKE '%Oranges%' THEN '1' END) AS Oranges
FROM t 
  ;

 APPLES    ORANGES
---------- ----------
         4          3

SQL> with t as
    (
 select 'Apples'            as fruits from dual union all
select 'Apples Oranges'    as fruits from dual union all
 select 'Apples Oranges'    as fruits from dual union all
select 'Apples'            as fruits from dual union all
select 'Oranges'           as fruits from dual
) select
     COUNT (CASE WHEN fruits LIKE '%Apples%' THEN '1' END) AS Apples ,
     COUNT (CASE WHEN fruits LIKE '%Oranges%' THEN '1' END) AS Oranges
FROM t   ;

    APPLES    ORANGES
---------- ----------
         4          3

SQL>