CODEWITHSUNDEEP
sqloracle-database
Greg
Greg

Reputation: 504

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

Upvotes: 0

Views: 140

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Either with sum or count, it works

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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>

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142710

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>

Upvotes: 1

Related Questions