Any missing instruction?

Question

Hi,

Using pgAdmin4, PostgreSQL 12

I want to get a table with 2 columns:

• the first one should contain the category of film lengths: "Less than 1 hour", "1 to 2 hours", "2 to 3 hours", "more than 3 hours"
• the second one should get the number of films depending on the categories in the first column

I just copied this query I saw in a training session video; the trainer got the desired results, but not me:

select film_id
, title
, length
, rating
, case
when length between 0 and 59 then 'Less then 1 hour'
when length between 60 and 119 then '1 to 2 hours'
when length between 120 and 179 then '2 to 3 hours'
else 'over 3 hours'
end as FilmLengthCategory
, count (film_id) as CountofFilms
from film
group by case
when length between 0 and 59 then 'Less then 1 hour'
when length between 60 and 119 then '1 to 2 hours'
when length between 120 and 179 then '2 to 3 hours'
else 'over 3 hours'
end
order by CountOfFilms desc

Can you please help me?

Answer 1

Subquery was the alias I gave. One more time:

select FilmLengthCategory, count(film_id) as CountOfFilms from (
 select film_id
 , title
 , length
 , rating
 , case
 when length between 0 and 59 then 'Less than 1 hour'
 when length between 60 and 119 then '1 to 2 hours'
 when length between 120 and 179 then '2 to 3 hours'
 else 'over 3 hours'
 end as FilmLengthCategory 
from film ) AS SubQuery
 GROUP BY FilmLengthCategory
 ORDER BY CountOfFilms

If this would not work in PostgreSQL, try using ORDER BY 2. Everything else is very basic SQL so I assume it should work the same in any database (hopefully).

Answer 2

Try:

select FilmLenthCategory, count(film_id) as CountOfFilms from (
select film_id
, title
, length
, rating
, case
when length between 0 and 59 then 'Less than 1 hour'
when length between 60 and 119 then '1 to 2 hours'
when length between 120 and 179 then '2 to 3 hours'
else 'over 3 hours'
end as FilmLengthCategory from film ) SubQuery
GROUP BY FilmLengthCategory
ORDER BY CountOfFilms

Answer 3

I updated the query, as the first columns don't need to be there.

Here is the updated query that should get the requested table, but I still don't see it:

select case
when length between 0 and 59 then 'Less then 1 hour'
when length between 60 and 119 then '1 to 2 hours'
when length between 120 and 179 then '2 to 3 hours'
else 'over 3 hours'
end as FilmLengthCategory
, count (film_id) as CountofFilms
from film
group by case
when length between 0 and 59 then 'Less then 1 hour'
when length between 60 and 119 then '1 to 2 hours'
when length between 120 and 179 then '2 to 3 hours'
else 'Over 3 hours'
end
order by CountOfFilms desc

What is missing?

Answer 4

This is a Microsoft SQL Server forum. For help with PostgreSQL, I suggest you post your questions in PostgreSQL forum.

https://www.postgresql.org/community/