Printing result of a query with regexp

I have records that starts with the word point and records that does not start with the word point.

I am trying to count how many of each and I have this query:

select count(*) from tbl
where regexp_like(name, '.*point.*')

The query above is working. I want to know how can I print the result like this:

names_with_point    names_without_point
  234                     120

CodePudding user response：

You can use count_if:

select count_if(regexp_like(name, '.*point.*')) as names_with_point
    , count_if(not regexp_like(name, '.*point.*')) as names_without_point
from dataset

CodePudding user response：

Not sure count_if is an ANSI SQL function.

select count(case when regexp_like(name, '.*point.*') THEN name end) as  names_with_point,
       count(case when not regexp_like(name, '.*point.*') THEN name end ) as names_without_point
          from dataset