Cómo usar GROUP BY y ORDER BY en SQL

Introducción

Las bases de datos de Structured Query Language (SQL) pueden almacenar y gestionar una gran cantidad de datos en numerosas tablas. Con conjuntos de datos grandes, es importante entender cómo ordenar los datos, especialmente para analizar conjuntos de resultados u organizar datos para informes o comunicaciones externas.

Dos declaraciones comunes en SQL que ayudan con la ordenación de sus datos son GROUP BY y ORDER BY. Una declaración GROUP BY ordena los datos agrupándolos según la(s) columna(s) que especifique en la consulta y se utiliza con funciones de agregado. Un ORDER BY le permite organizar conjuntos de resultados alfabética o numéricamente y en orden ascendente o descendente.

En este tutorial, ordenará los resultados de la consulta en SQL utilizando las declaraciones GROUP BY y ORDER BY. También practicará la implementación de funciones de agregado y la cláusula WHERE en sus consultas para ordenar aún más los resultados.

Requisitos previos

Para seguir esta guía, necesitarás una computadora que ejecute algún tipo de sistema de gestión de base de datos relacional (RDBMS) que utilice SQL. Las instrucciones y ejemplos en este tutorial fueron validados utilizando el siguiente entorno:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL instalado y asegurado en el servidor. Sigue nuestra guía Cómo instalar MySQL en Ubuntu 20.04 para configurarlo. Esta guía asume que también has configurado un usuario de MySQL no root, como se describe en el Paso 3 de esta guía.

Para practicar la clasificación de resultados de datos en este tutorial, necesitarás una base de datos y una tabla cargadas con datos de muestra. Si no tienes una lista para insertar, puedes leer la siguiente sección Conexión a MySQL y Configuración de una Base de Datos de Muestra para aprender cómo crear una base de datos y una tabla. Este tutorial hará referencia a esta base de datos y tabla de muestra en todo momento.

Conexión a MySQL y Configuración de una Base de Datos de Muestra

Si tu base de datos SQL se ejecuta en un servidor remoto, accede por SSH a tu servidor desde tu máquina local:

ssh sammy@your_server_ip

A continuación, abre el prompt de MySQL, reemplazando sammy con la información de tu cuenta de usuario de MySQL:

mysql -u sammy -p

Crea una base de datos llamada movieDB:

CREATE DATABASE movieDB;

Si la base de datos se creó correctamente, recibirás la siguiente salida:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos movieDB, ejecuta la siguiente instrucción USE:

USE movieDB;

Output
Database changed

Después de seleccionar la base de datos, crea una tabla dentro de ella. Para el ejemplo de este tutorial, crearemos una tabla que almacene información sobre las proyecciones de un cine local. Esta tabla tendrá las siguientes siete columnas:

• theater_id: almacena valores del tipo de datos int para las salas de proyección de cada cine, y servirá como la clave primaria de la tabla, lo que significa que cada valor en esta columna funcionará como un identificador único para su fila respectiva.
• date: utiliza el tipo de datos DATE para almacenar la fecha específica en la que se mostró una película, con el formato de cuatro dígitos para el año y un máximo de dos dígitos para el mes y el día (YYYY-MM-DD).
• time: representa la hora programada de la proyección de la película con el tipo de datos TIME, con el formato de horas, minutos y segundos (HH:MM:SS).
• movie_name: almacena el nombre de la película utilizando el tipo de datos varchar con un máximo de 40 caracteres.
• movie_genre: utiliza el tipo de datos varchar con un máximo de 30 caracteres, para contener información sobre el género respectivo de cada película.
• guest_total: muestra el número total de invitados que asistieron a la proyección de una película con el tipo de datos int.
• ticket_cost: utiliza el tipo de datos decimal, con una precisión de cuatro y una escala de uno, lo que significa que los valores en esta columna pueden tener cuatro dígitos y dos dígitos a la derecha del punto decimal. Esta columna representa el costo del boleto para la proyección específica de la película.

Crea una tabla llamada movie_theater que contenga cada una de estas columnas ejecutando el siguiente comando CREATE TABLE:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

A continuación, inserta algunos datos de ejemplo en la tabla vacía:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Una vez que hayas insertado los datos, estás listo para comenzar a ordenar los resultados de la consulta en SQL.

Usando GROUP BY

La función de una declaración GROUP BY es agrupar registros con valores compartidos. Una declaración GROUP BY siempre se utiliza con una función de agregación en una consulta. Como recordarás, una función de agregación resume información y devuelve un único resultado. Por ejemplo, puedes consultar el recuento total o la suma de una columna y esto producirá un único valor en tu resultado. Con una cláusula GROUP BY, puedes implementar la función de agregación para obtener un valor de resultado para cada grupo que desees.

GROUP BY es útil para devolver múltiples resultados deseados ordenados por su(s) grupo(s) especificado(s), en lugar de solamente una columna. Además, GROUP BY siempre debe venir después de la declaración FROM y la cláusula WHERE, si decides usar una. Aquí tienes un ejemplo de cómo se estructura una consulta con un GROUP BY y una función de agregado:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Para ilustrar cómo puedes usar las declaraciones GROUP BY, digamos que estás liderando la campaña de varios estrenos de películas y quieres evaluar el éxito de tus esfuerzos de marketing. Le pides a un cine local que comparta los datos que recopilaron de los invitados el viernes y el sábado. Comienza revisando los datos ejecutando SELECT y el símbolo * para seleccionar “cada columna” de la tabla movie_theater:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Aunque estos datos son útiles, quieres realizar una evaluación más profunda y ordenar los resultados para algunas columnas específicas.

Dado que trabajaste en películas de varios géneros diferentes, estás interesado en saber qué tan bien fueron recibidas por los espectadores. Específicamente, quieres saber el promedio de personas que vieron cada género de película. Usa SELECT para recuperar los diversos tipos de películas de la columna movie_genre. Luego, aplica la función de agregado AVG en la columna guest_total, usa AS para crear un alias para una columna llamada average e incluye la declaración GROUP BY para agrupar los resultados por movie_genre. Agruparlos de esta manera te proporcionará los resultados promedio para cada género de película:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

Esta salida proporciona los cuatro promedios para cada género dentro del grupo movie_genre. Según esta información, las películas de Acción atrajeron el mayor número promedio de invitados por proyección.

A continuación, supongamos que deseas medir los ingresos del teatro durante dos días separados. La siguiente consulta devuelve valores de la columna date, así como valores devueltos por la función de agregado SUM. Específicamente, la función de agregado SUM encerrará una ecuación matemática entre paréntesis para multiplicar (usando el operador *) el número total de invitados por el costo de un boleto, representado como: SUM(guest_total * ticket_cost). Esta consulta incluye la cláusula AS para proporcionar el alias total_revenue para la columna devuelta por la función de agregado. Luego completa la consulta con la declaración GROUP BY para agrupar los resultados de la consulta por la columna date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Dado que usaste GROUP BY para agrupar la columna date, tu salida proporciona los resultados para los ingresos totales en ventas de boletos para cada día, en este caso, $7,272 para el viernes 27 de mayo y $9,646 para el sábado 28 de mayo.

Ahora imagina que quieres enfocarte y analizar una película: Los Malos. En este escenario, quieres averiguar cómo afectan el momento y los puntos de precio a la elección de una familia para ver una película animada. Para esta consulta, usa la función de agregación MAX para recuperar el costo de entrada máximo, asegurándote de incluir AS para crear el alias de la columna datos_de_precio. Después, usa la cláusula WHERE para reducir los resultados por nombre_de_pelicula únicamente a “Los Malos”, y usa AND para también determinar los horarios de películas más populares basados en números de total_de_invitados que fueron más de 100 con el operador de comparación >. Luego completa la consulta con la declaración GROUP BY y agrúpala por hora:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

Según esta salida, más invitados asistieron a la película Los Malos en el horario de la matiné temprana de las 9:00 a. m., que tenía un punto de precio más asequible de $8.00 por entrada. Sin embargo, estos resultados también muestran que los invitados pagaron el precio de entrada más alto de $13.00 a las 5:00 p. m., lo que sugiere que las familias prefieren proyecciones que no sean demasiado tarde en el día y estarán dispuestas a pagar un poco más por una entrada. Esto parece ser una evaluación justa en comparación con el horario de las 10:00 p. m., cuando la película Los Malos solo tuvo 83 invitados y el precio por entrada fue de $18.00. Esta información puede ser útil para proporcionar al gerente del cine evidencia de que abrir más franjas horarias de matiné y tarde temprano puede aumentar la asistencia de las familias que están tomando una decisión basada en un horario y punto de precio preferidos.

Tenga en cuenta que aunque GROUP BY casi siempre se usa con una función de agregado, puede haber excepciones, aunque es poco probable. Sin embargo, si quisiera agrupar sus resultados sin una función de agregado, puede usar la declaración DISTINCT para lograr el mismo resultado. Una cláusula DISTINCT elimina cualquier duplicado en un conjunto de resultados al devolver los valores únicos en la columna, y solo se puede usar con una declaración SELECT. Por ejemplo, si quisiera agrupar todas las películas juntas por nombre, podría hacerlo con la siguiente consulta:

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Como recordará al ver todos los datos en la tabla, había duplicados de los nombres de las películas ya que había múltiples proyecciones. Por lo tanto, DISTINCT eliminó esos duplicados y agrupó efectivamente los valores únicos bajo la columna única movie_name. Esto es efectivamente idéntico a la siguiente consulta, que incluye una declaración GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Ahora que ha practicado el uso de GROUP BY con funciones de agregado, a continuación aprenderá cómo ordenar los resultados de su consulta con la declaración ORDER BY.

USANDO ORDER BY

La función de la declaración ORDER BY es ordenar los resultados en orden ascendente o descendente según la(s) columna(s) que especifiques en la consulta. Dependiendo del tipo de datos almacenados por la columna que especifiques después de ella, ORDER BY los organizará en orden alfabético o numérico. Por defecto, ORDER BY ordenará los resultados en orden ascendente; sin embargo, si prefieres orden descendente, debes incluir la palabra clave DESC en tu consulta. También puedes usar la declaración ORDER BY con GROUP BY, pero debe venir después para funcionar correctamente. Similar a GROUP BY, ORDER BY también debe venir después de la declaración FROM y la cláusula WHERE. La sintaxis general para usar ORDER BY es la siguiente:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Continuemos con los datos de muestra para el cine y practiquemos ordenar resultados con ORDER BY. Comienza con la siguiente consulta que recupera valores de la columna guest_total y organiza esos valores numéricos con una declaración ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Dado que tu consulta especificó una columna con valores numéricos, la declaración ORDER BY organizó los resultados en orden numérico ascendente, comenzando con 25 bajo la columna guest_total.

Si prefirieras ordenar la columna en orden descendente, agregarías la palabra clave DESC al final de la consulta. Además, si quisieras ordenar los datos por los valores de caracteres bajo movie_name, lo especificarías en tu consulta. Realicemos ese tipo de consulta usando ORDER BY para ordenar la columna movie_name con valores de caracteres en orden descendente. Ordena los resultados aún más incluyendo una cláusula WHERE para recuperar los datos sobre las películas que se muestran a las 10:00 pm desde la columna time:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Este conjunto de resultados enumera las cuatro diferentes proyecciones de películas a las 10:00 pm en orden alfabético descendente, comenzando desde Top Gun Maverick hasta Downtown Abbey A New Era.

Para esta próxima consulta, combina las declaraciones ORDER BY y GROUP BY con la función de agregado SUM para generar resultados sobre los ingresos totales recibidos por cada película. Sin embargo, digamos que el cine contó erróneamente el total de invitados y olvidó incluir fiestas especiales que habían comprado y reservado entradas para un grupo de 12 personas en cada proyección.

En esta consulta, use SUM e incluya los 12 huéspedes adicionales en cada proyección de película mediante la implementación del operador de adición + y luego sumando 12 al guest_total. Asegúrese de encerrar esto entre paréntesis. Luego, multiplique este total por el ticket_cost con el operador * y complete la ecuación matemática cerrando el paréntesis al final. Agregue la cláusula AS para crear el alias de la nueva columna titulada total_revenue. Luego, use GROUP BY para agrupar los resultados de total_revenue para cada película basada en los datos recuperados de la columna movie_name. Por último, use ORDER BY para organizar los resultados bajo la nueva columna total_revenue en orden ascendente:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Este conjunto de resultados nos dice los ingresos totales para cada película con las ventas adicionales de boletos para 12 huéspedes y organiza las ventas totales de boletos en orden ascendente de menor a mayor. A partir de esto, aprendemos que Top Gun Maverick recibió la mayor cantidad de ventas de boletos, mientras que Men recibió la menor. Mientras tanto, las películas The Bad Guys y Downton Abbey A New Era estuvieron muy cerca en ventas totales de boletos.

En esta sección, practicaste varias formas de implementar la declaración ORDER BY y cómo especificar el orden que prefieres, como órdenes ascendentes y descendentes tanto para valores de datos de caracteres como numéricos. También aprendiste cómo incluir la cláusula WHERE para reducir tus resultados y realizaste una consulta usando tanto las declaraciones GROUP BY como ORDER BY con una función de agregación y una ecuación matemática.

Conclusión

Entender cómo usar las declaraciones GROUP BY y ORDER BY es importante para ordenar tus resultados y datos. Ya sea que desees organizar múltiples resultados bajo un grupo, organizar una de tus columnas en orden alfabético y descendente, o hacer ambas cosas simultáneamente; depende de ti y de tu(s) resultado(s) deseado(s). También aprendiste sobre otras formas de ordenar tus resultados aún más con la cláusula WHERE. Si deseas aprender más, consulta nuestro tutorial sobre Cómo Usar Comodines en SQL para practicar filtrando resultados con la cláusula LIKE.