***********
- ***************************************************************************************************
- *****************************************
- *****************************
- ***************************************************************************
**********
************************************************************************
*************************** - ***************************************************************************
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
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
Expected results
Your results