CODEWITHSUNDEEP
sqloracle-database
user11623013
user11623013

Reputation: 3

Counting records that contain letters given in (SQL)

I have to count records containing given letters, for example column A will contain count of records containing 'a' or 'A', and for E it will be count of records containing 'e' or 'E'. Is there any way to do this by only using grouping functions?

I can do this by using subqueries, but we had this task in class before learning subqueries and I have no idea how to do this by grouping.

The result of the code below that I want to achieve by using grouping:

enter image description here

select
(select count(*) from table where lower(name) like '%a%') as a, 
(select count(*) from table where lower(name) like '%e%') as e
from dual;

Upvotes: 0

Views: 2059

Answers (2)

Wei Lin
Wei Lin

Reputation: 3811

you can use count + case to avoid repeating full-table query select 

select count(case when lower(name) like '%a%' then 1  end) as a
   ,count(case when lower(name) like '%e%' then 1 end) as e
from Table

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

The proper expression uses sum():

select sum(case when lower(name) like '%a%' then 1 else 0 end) as num_a,
       sum(case when lower(name) like '%e%' then 1 else 0 end) as num_e
from t;

You can also use regular expressions (although they are probably more expensive than like for this purpose):

select sum(case when regexp_like(name, '[aA]') then 1 else 0 end) as num_a,
       sum(case when regexp_like(name, '[eE]') then 1 else 0 end) as num_e
from t;

Upvotes: 0

Related Questions