Группировки. Group By, Having (35 мин.)

Смотреть код

select
      rating,
      count(*) films_count,
      sum(length) as total_length,
      max(length) as max_length,
      min(length) as min_length,
      avg(length) as avg_length,
      bool_and(length < 185) ,
      bool_or(length >= 185),
      string_agg(title, '; ')
from
      film f
group by
      rating;

select distinct 
     rating
from 
    film;

select 
count(*),
sum(length)
from 
film f;

select 
rating,
rental_rate,
substring(title, 1, 1),
count(*)
from 
film f
group by
rating,
rental_rate,
substring(title, 1, 1)
order by 
rating,
rental_rate;

select 

title,
count(*)
from 
inventory i
join film f
using (film_id)
group by
title;

select 
f.title,
f.film_id,
i.film_id
from 
film f
left join inventory i 
on
f.film_id = i.film_id
order by 
i.film_id;

select 
f.title,
count(i.film_id) film_count
from 
film f
left join inventory i 
on
f.film_id = i.film_id
group by 
f.title
order by
film_count;

select 
f.title,
count(fc.film_id) as category_count
from 
film f
left join film_category fc 
on
f.film_id = fc.film_id
group by 
f.title
order by 
category_count;

select 
a.first_name || ' ' || a.last_name as actor_name,
fa.film_id,
fc.category_id
from 
actor a
join film_actor fa
using(actor_id)
join film_category fc
using(film_id);

select 
a.first_name || ' ' || a.last_name as actor_name,
count(*) as film_count,
count(distinct fc.category_id) as category_count
from 
actor a
join film_actor fa
using(actor_id)
join film_category fc
using(film_id)
group by
a.first_name || ' ' || a.last_name;

select 
f.title,
count(*) as payment_count
from 
film f
join inventory i on
f.film_id = i.film_id
join rental r on
r.inventory_id = i.inventory_id
join payment p on
p.rental_id = r.rental_id
where 
f.rental_rate > 2
group by 
f.title
having 
count(*) > 10
order by 
payment_count;