Ejercicio resuelto: JOIN de tres tablas con GROUP BY y HAVING en SQL

Consulta SQL resuelta paso a paso: une barrios, hortelanos y cosechas, agrega con SUM y COUNT DISTINCT y filtra grupos con HAVING en SQLite.

Código de Examen SQL Bases de datos Nivel básico Código probado

Código de Examen: Ejercicio resuelto: JOIN de tres tablas con GROUP BY y HAVING en SQL

Bases de Datos I · Prácticas de laboratorioEjercicio 2 · 2,5 puntos · 20 min

El ayuntamiento gestiona una red de huertos urbanos repartidos por varios barrios. Cada vecino que cultiva una parcela es un hortelano y pertenece a un único barrio. Cada vez que un hortelano recoge producto, se anota una cosecha con el cultivo, los kilos y la fecha. La concejalía quiere premiar a los barrios donde la actividad ha sido colectiva y productiva durante el año 2024.

El script de entrada crea y rellena tres tablas: barrio(id, nombre), hortelano(id, nombre, barrio_id), donde barrio_id referencia a barrio.id, y cosecha(id, hortelano_id, cultivo, kg, fecha), donde hortelano_id referencia a hortelano.id, kg es un número real positivo y fecha es un texto con formato AAAA-MM-DD. Ninguna columna admite NULL.

Escribe una única consulta SQL (SQLite 3) que, considerando solo las cosechas cuya fecha pertenece al año 2024, devuelva una fila por barrio con estas columnas, exactamente con estos nombres y en este orden: barrio (nombre del barrio), hortelanos (número de hortelanos distintos del barrio con al menos una cosecha en 2024), cosechas (número de cosechas de 2024 del barrio) y kg_total (suma de kilos de esas cosechas).

Solo deben aparecer los barrios que cumplan a la vez estas dos condiciones: tener al menos 2 hortelanos activos en 2024 y sumar al menos 20 kilos en 2024. Los dos umbrales son inclusivos. Un barrio sin hortelanos, o cuyos hortelanos no cosecharon en 2024, no aparece.

Ordena el resultado por kg_total de mayor a menor y, si dos barrios empatan en kilos, por barrio en orden alfabético ascendente. El corrector imprime una cabecera con los nombres de columna y una fila por resultado, con los valores separados por |; los reales se muestran con dos decimales, salvo los que son enteros, que se muestran sin decimales. Puedes suponer que en cada caso de prueba al menos un barrio cumple las condiciones.

Entrada de ejemplo

CREATE TABLE barrio (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL);
CREATE TABLE hortelano (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL, barrio_id INTEGER NOT NULL REFERENCES barrio(id));
CREATE TABLE cosecha (id INTEGER PRIMARY KEY, hortelano_id INTEGER NOT NULL REFERENCES hortelano(id), cultivo TEXT NOT NULL, kg REAL NOT NULL, fecha TEXT NOT NULL);
INSERT INTO barrio VALUES (1,'Centro'),(2,'Arrabal'),(3,'Ribera'),(4,'Lomas');
INSERT INTO hortelano VALUES (1,'Ana',1),(2,'Bruno',1),(3,'Carmen',2),(4,'David',2),(5,'Elena',2),(6,'Fermin',3),(7,'Gloria',4);
INSERT INTO cosecha VALUES (1,1,'tomate',8.5,'2024-07-10'),(2,1,'pimiento',3.25,'2024-08-02'),(3,2,'calabacin',12.0,'2024-06-20'),(4,2,'tomate',5.0,'2023-09-01'),(5,3,'lechuga',4.5,'2024-04-15'),(6,4,'patata',15.75,'2024-07-30'),(7,4,'cebolla',6.0,'2024-08-12'),(8,6,'tomate',30.0,'2024-08-01'),(9,7,'judia',2.0,'2024-05-05');

Salida esperada

barrio|hortelanos|cosechas|kg_total
Arrabal|2|3|26.25
Centro|2|3|23.75

Encadena correctamente las tres tablas con JOIN y condiciones de unión por clave ajena: 0,5
Filtra las cosechas de 2024 en el WHERE, antes de agrupar, con un rango de fechas correcto: 0,5
Agrupa por barrio y calcula bien los agregados: COUNT(DISTINCT) para hortelanos, COUNT(*) para cosechas y SUM para los kilos: 0,75
Expresa las dos condiciones sobre el grupo en HAVING (al menos 2 hortelanos y al menos 20 kg, ambos inclusivos): 0,5
Ordena por kilos descendente y, en caso de empate, por nombre de barrio ascendente, con los nombres de columna pedidos: 0,25

Pistas

Pista 1 · ¿Cuántas tablas necesito y en qué orden las uno?

El nombre del barrio está en barrio y los kilos están en cosecha, pero no hay ninguna columna que las relacione directamente. La tabla hortelano hace de puente: une barrio.id con hortelano.barrio_id y hortelano.id con cosecha.hortelano_id. Un JOIN interno (no LEFT JOIN) ya descarta por sí solo a los hortelanos sin cosechas, y eso es justo lo que pide el enunciado.

Pista 2 · ¿La condición de 2024 va en WHERE o en HAVING?

Pregúntate si la condición habla de una fila o de un grupo. Que una cosecha sea de 2024 es una propiedad de cada fila: se decide antes de agrupar y va en WHERE. Que un barrio sume 20 kilos es una propiedad del grupo y solo se puede saber después de agrupar. Para el año, como las fechas son texto AAAA-MM-DD, una comparación de rango entre dos cadenas funciona porque el orden alfabético coincide con el cronológico.

Pista 3 · ¿Por qué COUNT(*) no me sirve para contar hortelanos?

Después del JOIN, cada fila es una cosecha, no un hortelano. Un hortelano con cuatro cosechas aparece cuatro veces en el grupo de su barrio. COUNT(*) cuenta filas, y por tanto cosechas. Para contar personas distintas necesitas COUNT(DISTINCT h.id), y esa misma expresión debe repetirse en el HAVING.

Solución

Solución explicada

La consulta sigue el orden lógico de evaluación de SQL: primero se construye el producto filtrado de las tres tablas (FROM + JOIN), luego se descartan filas (WHERE), después se forman grupos (GROUP BY), se descartan grupos (HAVING) y, por último, se calculan las columnas y se ordena. Si tienes este orden en la cabeza, casi todas las decisiones del ejercicio salen solas. Si quieres practicar más consultas de este estilo, tienes otros casos en 10 Ejercicios de SQL Resueltos: JOINs, Subconsultas y Agrupaciones.

SELECT b.nombre               AS barrio,
       COUNT(DISTINCT h.id)   AS hortelanos,
       COUNT(*)               AS cosechas,
       SUM(c.kg)              AS kg_total
FROM barrio AS b
JOIN hortelano AS h ON h.barrio_id = b.id
JOIN cosecha   AS c ON c.hortelano_id = h.id
WHERE c.fecha >= '2024-01-01'
  AND c.fecha <  '2025-01-01'
GROUP BY b.id, b.nombre
HAVING COUNT(DISTINCT h.id) >= 2
   AND SUM(c.kg) >= 20
ORDER BY kg_total DESC, barrio ASC;

Las uniones. El FROM barrio AS b seguido de JOIN hortelano AS h ON h.barrio_id = b.id asocia cada barrio con sus vecinos, y JOIN cosecha AS c ON c.hortelano_id = h.id asocia cada vecino con sus cosechas. El resultado intermedio tiene una fila por cosecha, enriquecida con los datos de su hortelano y de su barrio. Como los dos JOIN son internos, un barrio sin hortelanos o un hortelano sin cosechas no generan ninguna fila. En el ejemplo, Elena (Arrabal) no tiene cosechas y desaparece aquí sin que tengamos que hacer nada.

El filtro de fecha. La condición c.fecha >= '2024-01-01' AND c.fecha < '2025-01-01' se evalúa fila a fila, antes de agrupar. Comparar cadenas funciona porque el formato AAAA-MM-DD ordena igual alfabética que cronológicamente. Usar el límite superior exclusivo '2025-01-01' es más robusto que escribir <= '2024-12-31', porque seguiría siendo correcto si algún día la columna incluyera hora. En el ejemplo, la cosecha de Bruno de 2023 queda fuera: si no se filtrara, Centro sumaría 28.75 kg en lugar de 23.75.

La agrupación. GROUP BY b.id, b.nombre forma un grupo por barrio. Agrupar también por b.id evita mezclar dos barrios que pudieran compartir nombre, y mantener b.nombre en la lista permite mostrarlo sin depender de extensiones del gestor. Dentro de cada grupo, COUNT(DISTINCT h.id) cuenta vecinos distintos, COUNT(*) cuenta filas, es decir, cosechas, y SUM(c.kg) suma los kilos.

El filtro de grupos. El HAVING exige a la vez COUNT(DISTINCT h.id) >= 2 y SUM(c.kg) >= 20. El caso «barrio con un solo hortelano muy productivo» comprueba lo primero: en Vega, Pablo registra cuatro cosechas y 40 kg, pero el barrio no aparece porque solo hay una persona. Si se hubiera usado COUNT(*), Vega habría salido con un 4 indebido. El caso «umbral exacto y empate» comprueba lo segundo: Norte y Sur suman exactamente 20 y entran los dos, mientras que Este se queda en 19.75 y no entra.

El orden. ORDER BY kg_total DESC, barrio ASC usa los alias del SELECT, algo que SQLite y el estándar permiten en ORDER BY (no en WHERE). El segundo criterio solo actúa en los empates: en el caso de umbral exacto, Norte va antes que Sur. Fíjate también en cómo se imprime kg_total: 20 aparece sin decimales por ser un real entero, mientras que 30.5 aparece como 30.50. En el caso «varios barrios y orden», Puerto queda fuera aunque Yago cosechara 50 kg, porque esa cosecha es de 2023 y en 2024 solo queda Zoe.

Código y tests en GitLab

Casos de prueba

Caso	Entrada	Salida esperada	Salida real	Resultado
Ejemplo del enunciado	CREATE TABLE barrio (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL); CREATE TABLE hortelano (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL, barrio_id INTEGER NOT NULL REFERENCES barrio(id)); CREATE TABLE cosecha (id INTEGER PRIMARY KEY, hortelano_id INTEGER NOT NULL REFERENCES hortelano(id), cultivo TEXT NOT NULL, kg REAL NOT NULL, fecha TEXT NOT NULL); INSERT INTO barrio VALUES (1,'Centro'),(2,'Arrabal'),(3,'Ribera'),(4,'Lomas'); INSERT INTO hortelano VALUES (1,'Ana',1),(2,'Bruno',1),(3,'Carmen',2),(4,'David',2),(5,'Elena',2),(6,'Fermin',3),(7,'Gloria',4); INSERT INTO cosecha VALUES (1,1,'tomate',8.5,'2024-07-10'),(2,1,'pimiento',3.25,'2024-08-02'),(3,2,'calabacin',12.0,'2024-06-20'),(4,2,'tomate',5.0,'2023-09-01'),(5,3,'lechuga',4.5,'2024-04-15'),(6,4,'patata',15.75,'2024-07-30'),(7,4,'cebolla',6.0,'2024-08-12'),(8,6,'tomate',30.0,'2024-08-01'),(9,7,'judia',2.0,'2024-05-05');	`barrio\|hortelanos\|cosechas\|kg_total Arrabal\|2\|3\|26.25 Centro\|2\|3\|23.75`	`barrio\|hortelanos\|cosechas\|kg_total Arrabal\|2\|3\|26.25 Centro\|2\|3\|23.75`	OK
Umbral exacto y empate	CREATE TABLE barrio (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL); CREATE TABLE hortelano (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL, barrio_id INTEGER NOT NULL REFERENCES barrio(id)); CREATE TABLE cosecha (id INTEGER PRIMARY KEY, hortelano_id INTEGER NOT NULL REFERENCES hortelano(id), cultivo TEXT NOT NULL, kg REAL NOT NULL, fecha TEXT NOT NULL); INSERT INTO barrio VALUES (1,'Sur'),(2,'Norte'),(3,'Este'); INSERT INTO hortelano VALUES (1,'Irene',2),(2,'Jorge',2),(3,'Lucia',1),(4,'Mario',1),(5,'Nuria',3),(6,'Oscar',3); INSERT INTO cosecha VALUES (1,1,'acelga',10.0,'2024-03-01'),(2,2,'puerro',10.0,'2024-12-31'),(3,3,'tomate',15.5,'2024-01-01'),(4,4,'haba',4.5,'2024-10-10'),(5,5,'patata',19.5,'2024-02-02'),(6,6,'perejil',0.25,'2024-11-11'),(7,6,'calabaza',5.0,'2025-01-01');	`barrio\|hortelanos\|cosechas\|kg_total Norte\|2\|2\|20 Sur\|2\|2\|20`	`barrio\|hortelanos\|cosechas\|kg_total Norte\|2\|2\|20 Sur\|2\|2\|20`	OK
Un solo hortelano muy productivo	CREATE TABLE barrio (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL); CREATE TABLE hortelano (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL, barrio_id INTEGER NOT NULL REFERENCES barrio(id)); CREATE TABLE cosecha (id INTEGER PRIMARY KEY, hortelano_id INTEGER NOT NULL REFERENCES hortelano(id), cultivo TEXT NOT NULL, kg REAL NOT NULL, fecha TEXT NOT NULL); INSERT INTO barrio VALUES (1,'Vega'),(2,'Molino'); INSERT INTO hortelano VALUES (1,'Pablo',1),(2,'Rosa',2),(3,'Sara',2); INSERT INTO cosecha VALUES (1,1,'tomate',10.0,'2024-06-01'),(2,1,'tomate',10.0,'2024-07-01'),(3,1,'pimiento',10.0,'2024-08-01'),(4,1,'calabacin',10.0,'2024-09-01'),(5,2,'lechuga',7.5,'2024-04-01'),(6,2,'lechuga',7.5,'2024-05-01'),(7,3,'cebolla',6.0,'2024-06-15');	`barrio\|hortelanos\|cosechas\|kg_total Molino\|2\|3\|21`	`barrio\|hortelanos\|cosechas\|kg_total Molino\|2\|3\|21`	OK
Varios barrios y orden	CREATE TABLE barrio (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL); CREATE TABLE hortelano (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL, barrio_id INTEGER NOT NULL REFERENCES barrio(id)); CREATE TABLE cosecha (id INTEGER PRIMARY KEY, hortelano_id INTEGER NOT NULL REFERENCES hortelano(id), cultivo TEXT NOT NULL, kg REAL NOT NULL, fecha TEXT NOT NULL); INSERT INTO barrio VALUES (1,'Alto'),(2,'Bajo'),(3,'Puerto'),(4,'Huerta'); INSERT INTO hortelano VALUES (1,'Teo',1),(2,'Ursula',1),(3,'Victor',1),(4,'Wenda',2),(5,'Xavi',2),(6,'Yago',3),(7,'Zoe',3); INSERT INTO cosecha VALUES (1,1,'tomate',12.0,'2024-05-01'),(2,2,'judia',8.0,'2024-06-01'),(3,3,'pepino',5.5,'2024-07-01'),(4,4,'patata',30.0,'2024-08-01'),(5,5,'albahaca',0.5,'2024-08-02'),(6,6,'calabaza',50.0,'2023-12-31'),(7,7,'tomate',25.0,'2024-09-09');	`barrio\|hortelanos\|cosechas\|kg_total Bajo\|2\|2\|30.50 Alto\|3\|3\|25.50`	`barrio\|hortelanos\|cosechas\|kg_total Bajo\|2\|2\|30.50 Alto\|3\|3\|25.50`	OK

Salidas reales: código compilado con SQLite 3.46.1 y ejecutado en un contenedor aislado el 8 de noviembre de 2026.

Complejidad

Si llamamos B, H y C al número de filas de cada tabla, el coste lo domina la unión entre hortelano y cosecha. Con id como INTEGER PRIMARY KEY, SQLite puede recorrer las cosechas y localizar cada hortelano y su barrio por clave en tiempo logarítmico, lo que da un coste del orden de C·log H. El filtro de fecha recorre todas las cosechas una vez, salvo que exista un índice sobre cosecha(fecha), en cuyo caso solo se leen las de 2024.

La agrupación necesita mantener, por cada barrio, un acumulador para la suma y el conteo y un conjunto de identificadores para el COUNT(DISTINCT). La memoria adicional es, por tanto, del orden de B + H en el peor caso. La ordenación final solo afecta a los grupos supervivientes, como mucho B filas, con coste B·log B, despreciable frente al recorrido de las cosechas.

Errores típicos

Contar hortelanos con COUNT(*) o COUNT(h.id): ambos cuentan filas del JOIN, es decir, cosechas, y dejan pasar barrios con un único vecino muy activo.
Poner SUM(c.kg) >= 20 en el WHERE: es un error de sintaxis, porque los agregados no existen todavía cuando se filtran filas.
Mover el filtro de 2024 al HAVING o, peor, olvidarlo. Entonces se suman cosechas de otros años y aparecen barrios que no deberían, como Centro con 28.75 kg o Puerto en el caso de varios barrios.
Usar umbrales estrictos (> 20, > 1) cuando el enunciado dice «al menos». Con eso desaparecen Norte y Sur en el caso de umbral exacto.
Usar LEFT JOIN «por si acaso». No cambia el resultado aquí, porque el WHERE sobre c.fecha elimina las filas con NULL, pero demuestra que no se entiende qué hace cada tipo de unión.
Olvidar el criterio de desempate en ORDER BY. Con totales iguales, el orden de salida queda indefinido y la respuesta deja de ser determinista.

Variantes

Listar todos los barrios, también los que no tuvieron actividad

Si se pide una fila por cada barrio, con 0 hortelanos y 0 kg cuando no hay actividad, hay que usar LEFT JOIN desde barrio. Además, la condición de fecha debe moverse a la cláusula ON de la unión con cosecha: si se deja en el WHERE, las filas con NULL se descartan y el LEFT JOIN se convierte de hecho en interno. Los kilos se muestran con COALESCE(SUM(c.kg), 0), el COUNT(DISTINCT c.hortelano_id) devuelve 0 de forma natural y el HAVING desaparece.

Media de kilos por hortelano activo

Añade la columna ROUND(SUM(c.kg) * 1.0 / COUNT(DISTINCT h.id), 2) AS media_kg. El divisor debe ser el número de personas, no el de cosechas: AVG(c.kg) daría la media por cosecha, que es otra magnitud. Si el enunciado pide además que la media supere un valor, esa condición también va en el HAVING.

En los temarios

Las consultas con uniones, agrupaciones y filtros sobre grupos forman parte del bloque de SQL de varias asignaturas introductorias de bases de datos en Latinoamérica. El temario de Fundamentos de Bases de Datos de la Facultad de Ciencias de la UNAM incluye el uso de SQL para la definición y la consulta de datos. El programa de Bases de Datos del Instituto Politécnico Nacional (ISC 2020) dedica una unidad al lenguaje SQL, después del modelo relacional y del álgebra relacional.

En Argentina, la asignatura Gestión de Datos de la UTN Facultad Regional Córdoba recoge entre sus objetivos la ejercitación con sentencias SQL para definir y manipular datos. En Colombia, la página del curso ISIS2304 Sistemas Transaccionales de la Universidad de los Andes incluye bibliografía específica sobre consultas SQL. Este ejercicio encaja como práctica de ese tipo de contenidos.