Ejemplos agregados

-- El total de ciudades: contar 

select country,count(city) total
from country join city on country.country_id=city.country_id
group by country;

-- Total de importe de clientes

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id;

-- en la consulta anterior los clientes que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id
having total>100;

-- Los clientes cuyo nombre empieza por 'a' que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
where first_name like 'a%'
group by customer.customer_id
having total>100 and media>4;

-- ¿Cuantos países tienen más de 50 clientes?
-- Nombre del país y número de clientes (count)

select country, count(customer_id) total from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
group by country.country_id
having total>50