Instruction
- Write a query to return the number of films in the following categories: short, medium, and long.
- The order of your results doesn't matter.
- short: less
<60
minutes. - medium:
>=60
minutes, but<100
minutes. - long:
>=100
minutes
Table: film
col_name | col_type ----------------------+-------------------------- film_id | integer title | text description | text release_year | integer language_id | smallint original_language_id | smallint rental_duration | smallint rental_rate | numeric length | smallint replacement_cost | numeric rating | text
Sample results
film_category | count ---------------+------- medium | 1 long | 2 short | 3
Solution postgres
SELECT
CASE WHEN length < 60 THEN 'short'
WHEN length < 100 THEN 'medium'
WHEN length >= 100 THEN 'long'
ELSE NULL
END AS film_category,
COUNT(*)
FROM film
GROUP BY film_category;
Explanation
This query is selecting data from a table called "film". The query is using a CASE statement to categorize films based on their length. Films with a length less than 60 minutes will be categorized as "short", films with a length between 60 and 100 minutes will be categorized as "medium", and films with a length greater than or equal to 100 minutes will be categorized as "long". The query then counts the number of films in each category and groups the results by the film category. The output will show the number of films in each category.
Copied
Expected results
Your results