Etiqueta: group by

Estadísticas de países:

select country pais,count(amount),sum(amount) total, avg(amount) media,max(amount) máximo, min(amount) minimo, variance(amount),std(amount) from country join city using(country_id)
join address using(city_id)
join customer using(address_id)
join payment using(customer_id)
group by pais
order by media desc

Número de películas por actor:

SELECT first_name,last_name, count(film_id) total from actor left join film_actor using(actor_id)
left join film using(film_id)
group by first_name,last_name
order by first_name desc

Ciudades por país:

select country,count(city_id) total from country join city using(country_id)
group by country
order by total asc

Categorías con más de 60 películas:

select name,count(film_id) total from category join film_category using(category_id)
group by name
having total>=60

Actor que ha participado en más películas:

select first_name,last_name,count(film_id) total from actor join film_actor using(actor_id)
join film using(film_id)
group by actor_id
order by total desc
limit 1

Total de categorías por actor (diferentes):

select distinct first_name,last_name,count(distinct name) from actor join film_actor using(actor_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
group by actor_id

Películas alquiladas por clientes:

select first_name,last_name, count(film_id) total from film join inventory using(film_id)
join rental using(inventory_id)
join customer using(customer_id)
group by customer_id

Categoría más alquilada:

select name,count(customer_id) total from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
order by total desc
limit 1