Count the number of times word appears in a single column

I’m attempting to count the number of times apples and oranges appear in my fruit column. The table looks like this:

Fruit
-------
Apples
Apples Oranges
Apples Oranges
Apples
Oranges

Expected output:

Apples 4 
Oranges 3

My code thus far. I’m not sure how to do it when both appear and how to add them to the totals. I’m sure there is an easier way that this.

SELECT 
     COUNT (CASE WHEN Fruit LIKE '%Apples%' THEN '1' END) AS Apples
     COUNT (CASE WHEN Fruit LIKE '%Oranges%' THEN '1' END) AS Oranges
FROM Fruits

Cheers

Answer

If those fruits are single-worded and separated by a space, then such a generic approach might be interesting for you.

Lines #1 – 8 represent sample data; you already have that so you don’t type it. Code you might need starts at line #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>