GROUPING multiple LIKE string

Question

Data:

2015478 warning occurred at 20201403021545
2020179 error occurred at 20201303021545
2025480 timeout occurred at 20201203021545
2025481 timeout occurred at 20201103021545
2020482 error occurred at 20201473021545
2020157 timeout occurred at 20201403781545
2020154 warning occurred at 20201407851545
2027845 warning occurred at 20201403458745

In above data, there are 3 kinds of strings I am interested in warning, error and timeout Can we have a single query where it will group by string and give the count of occurrences as below

Output:

timeout 3
warning 3
error 2

I know I can write separate queries to find count individually. But interested in a single query Thanks

Answer 1

You can use filtered aggregation for that:

select count(*) filter (where the_column like '%timeout%') as timeout_count, 
       count(*) filter (where the_column like '%error%') as error_count, 
       count(*) filter (where the_column like '%warning%') as warning_count
from the_table;

This returns the counts in three columns rather then three rows as your indicated.

If you do need this in separate rows, you can use regexp_replace() to cleanup the string, then group by that:

select regexp_replace(the_column, '(.*)(warning|error|timeout)(.*)', '\2') as what,
       count(*) 
from the_table
group by what;