HAVING

-- Los clientes que han gastado más de 150 dolares
-- En una consulta normal para filtrar uso WHERE
-- En una consulta agrupada uso HAVING

select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 HAVING total>=150 -- Ponemos una condición sobre los totales
 order by first_name,last_name;
 
 select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 HAVING total>=150 and media>5 -- Condición compuesta
 order by first_name,last_name;
 
 -- Resumiendo, HAVING nos permite poner condiciones sobre los totales
 -- de las funciones de agregado
 
 -- ¡OJO! HAVING es como un WHERE para los totales
 -- No podemos usar WHERE para los totales ni HAVING para los campos
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 WHERE total>=150 -- Da error columna no encontrada
 group by customer.customer_id
 order by first_name,last_name;
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 HAVING amount<9 -- Da el mismo tipo de error 
 order by first_name,last_name;
 
 -- Esto no quiere decir que no podamos tener condiciones compuestas
 -- que usen WHERE y HAVING
 
 -- Quiero saber el total, media, etc de los clientes cuyo nombre
 -- empieza por 'A' y el total es mayor de 150
 -- Como construyo esto: pongo el select group by
 
select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
  group by customer.customer_id
 order by first_name,last_name;
 
 -- La condiciones de las columnas no calculadas con WHERE
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 WHERE first_name like 'a%'
 group by customer.customer_id
 order by first_name,last_name;
 
 -- La condiciones de las columnas  calculadas con HAVING
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 WHERE first_name like 'a%'
 group by customer.customer_id
 HAVING total>150
 order by first_name,last_name;