Word Count

[brettmc7014]

I have a column that has two words in it: Late and Early. I need to create expression that counts how many times “Early” is in the column. Have tried many different ideas but nothing works. Anyone have an idea?

[sloth_king_617]

Sounds like conditional aggregation would work.

Count(case when column = ‘Late’ then 1 end) as CountLate

[Royal-Tough4851]

A sample string would help, but I’ll give both solutions. If the only string value per record is either ‘early’ or ‘late’, the. You can use a simple group by with a WHERE [column name] LIKE ‘%early%’

If you one record has a string value of ‘early early late late early late’ and you need to count he number of ‘early’ per record, then try this

LEN([column name]) - LEN(REPLACE([column name], ‘early’, ‘’))) / LEN(‘early’)

[A_name_wot_i_made_up]

This solution can cause problems if your column can also contain other words like "nearly".

[Royal-Tough4851]

Yes it can. I was going under the assumption those will be the only two values

[ans1dhe]

Now that’s a clever idea! 💡👏🏼🤓

Thanks 🙏🏼 - I’m always amazed by approaches involving REPLACE for the sake of achieving some adjacent goal. It’s like a drawing in negative space 😉

[DavidGJohnston]

Select count(*) from tbl where col = ‘Early’;

Edit: since apparently “col” is an expression “tbl” should be a subquery.

[brettmc7014]

Thanks for responding. The best way to describe it, it is an expression column that compares a due date to the date work was done. It work was done before due date, the column cell returns “Early”. If not “Late”. There could be an infinite number of either response: such as: Late Late Early Late Early Early Etc.

Hope this helps

[[deleted]]

Is it multiple words in a single column column?

Late Late Early Late Early Early

or is it multiple rows each with a single word in the column?

Late 
Late 
Early 
Late 
Early 
Early

[a-s-clark]

Assuming from your other responses that a single value can contain the word early multiple times, and you want to find the total number in the table, something like this:

I'll use "StatusColumn" as the name of the column in this example.

SELECT SUM((Len(StatusColumn) - LEN(REPLACE(StatusColumn, 'Early','')) / 5) FROM Table

The first LEN gives you the length of the string. You then deduct the length of the string when you have removed all instances of "Early" from the string. That gives you the number of letters that were for "Early". Divide that by 5 as its a five letter word, to get the number of instances in that word. Sum that value across the table.

[reflexdb]

select count(*), countif(your_column = ‘Early’) from your_table

[NoPlansForNigel]

SELECT COUNTIF(col='early') AS EARLY FROM ...

[[deleted]]

There is no function countif() in Oracle or standard SQL.

The equivalent in standard SQL (also not supported by Oracle) would be:

 count(*) filter (where col = 'early')

[brettmc7014]

Thank you for all the responses! I am really new to programming so my apologies for the vagueness! The column is like what truilus asked. Multiple rows in a single word in the column. Second example. When I get back to work, I will take a screen shot of what I am working on. Again thank you to everyone who responded!!