SQLPad
Instruction
- 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
Expected results
Solution postgres
SELECT
CASE WHEN first_name LIKE 'A%' THEN 'a_actors'
WHEN first_name LIKE 'B%' THEN 'b_actors'
WHEN first_name LIKE 'C%' THEN 'c_actors'
ELSE 'other_actors'
END AS actor_category,
COUNT(*)
FROM actor
GROUP BY actor_category;
Explanation
This query is counting the number of actors in the "actor" table and grouping them into categories based on the first letter of their first name. If their first name starts with "A", they are classified as "a_actors", if it starts with "B", they are classified as "b_actors", if it starts with "C", they are classified as "c_actors", and all other actors are classified as "other_actors". The query then counts the number of actors in each category and returns the result.
Copied
Your results