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.

Answer

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
UNION
select 'other_actors'actor_category, sum(1)
from getFistChar
where ac not in ('a', 'b', 'c')