Practicando SQL (Parte 3)

Te encuentras en la tercer parte de la serie "Practicando SQL":

1. Configurando ambiente de trabajo y consultas básicas
2. Consultas con más de una tabla
3. Consultas con agrupados
4. Consultas anidadas

Continuemos esta serie. Ahora veremos como generar resultados que tengan agrupados. La siguiente consulta obtiene en cuántos films ha participado cada actor:

mysql> SELECT actor.actor_id, first_name, last_name, COUNT(*)
    -> FROM actor
    ->   INNER JOIN film_actor ON (actor.actor_id=film_actor.actor_id)
    -> GROUP BY actor.actor_id, first_name, last_name;

El resultado tiene 200 filas:

Lo relacionado a la cláusula FROM ... INNER JOIN ... ON lo revisamos en la parte 2, esto nos regresa todos los actores con los ids de sus films. NOTA: en la parte 2, usamos tres tablas actor, film_actor, film porque queríamos desplegar el nombre de la película; en este caso sólo necesitamos saber si participó en una película (pero no el nombre de la misma) por ello sólo usamos actor y film_actor.

La claúsula GROUP BY junta todos las filas que tengan el mismo valor en las columnas especificadas. En este caso junta todas las filas que tengan el mismo id del actor, nombre y apellido. Ahora, en conjunto con GROUP BY estamos usando COUNT(*) que cuenta todas las filas que fueron agrupadas en una sóla fila.

Veamos esto con un poco más de detalle. Como pueden ver en la figura de arriba el actor con id=1 tiene 19 films. Sin usar GROUP BY podemos ver los ids de sus 19 films.

mysql> SELECT actor.actor_id, first_name, last_name, film_id
    -> FROM actor
    ->   INNER JOIN film_actor ON (actor.actor_id=film_actor.actor_id)
    -> WHERE actor.actor_id=1;

Cuando usamos GROUP BY en la consulta de arriba, las 19 filas se consolidan en una sola, pero COUNT(*) nos permite saber cuántas filas fueron agrupadas en una sola.

Junto con la cláusula GROUP BY podemos usar otras operaciones como SUM o AVG (promedio). Para calcular cuánto ha gastado un cliente en renta de películas podemos usar SUM: primero relacionamos un cliente con todos sus pagos; después agrupamos las filas por cliente; y finalmente sumamos todas las cantidades (amount) para cada cliente.

mysql> SELECT customer.customer_id, first_name, last_name, SUM(amount)
    -> FROM customer
    ->  INNER JOIN payment ON (customer.customer_id=payment.customer_id)
    -> GROUP BY customer.customer_id, first_name, last_name;

Preguntas

¿Por qué usamos customer.customer_id en la lista de columnas de SELECT, en lugar de sólo customer_id?

Cuando unimos dos o más tablas en FROM, existe la posibilidad que haya columnas con el mismo nombre entre las tablas. En caso de haber más de una columna con el mismo nombre, debemos especificar la tabla para evitar ambigüedades. Por ejemplo, si en el último ejemplo cambiamos customer.customer_id por sólo customer_id en el SELECT, obtendremos el siguiente error:

ERROR 1052 (23000): Column 'customer_id' in field list is ambiguous

En el resultado de la primera consulta, la última columna tiene como nombre COUNT(*), ¿puedo cambiarle el nombre a num_films?

Para renombrar el nombre de una columna puedes usar la palabra clave AS. En este ejemplo, en la primer consulta puedes cambiar el primer renglón por:

SELECT actor.actor_id, first_name, last_name, COUNT(*) AS num_films

Siguiente post...

Cerraremos esta serie con un ejemplo de consulta anidada.