Skip to content

SQL Substring Case Condition

I’m trying to solve the following question on SQLPAD.

Write a query to return the number of actors whose first name starts with ‘A’, ‘B’, ‘C’, or others. The order of your results doesn’t matter. You need to return 2 columns: The first column is the group of actors based on the first letter of their first_name, use the following: ‘a_actors’, ‘b_actors’, ‘c_actors’, ‘other_actors’ to represent their groups. Second column is the number of actors whose first name matches the pattern.

Table: actor

  col_name   | col_type
 actor_id    | integer
 first_name  | text
 last_name   | text

Sample results

actor_category | count
 a_actors       |    13
 b_actors       |     8

So far I’ve tried this:

select  CONCAT(substring(lower(first_name), 2, 1), '_actors') as actor_category , count(*)
FROM actor
group by actor_category

Not sure how to check the others condition.



you need this. The question actually says specific for a,b,c and others. You are not doing specific for a.b.c

with getFistChar AS
    select substring(LOWER(first_name), 1, 1) ac
    from actor
select CONCAT(ac, '_actors')actor_category, COUNT(1)count
from getFistChar
where ac in ('a', 'b', 'c')
Group By actor_category
select 'other_actors'actor_category, sum(1)
from getFistChar
where ac not in ('a', 'b', 'c')
4 People found this is helpful