Grouping with GROUP BY · Agrupación con GROUP BY
Grouping rows with GROUP BY
An aggregate like AVG normally summarises the whole table into one number. GROUP BY splits the rows into groups that share a value, and gives one summary row per group:
This gives one row for each form, with that form's count and average.
Agrupar filas con GROUP BY
Un agregado como AVG normalmente resume la tabla completa en un solo número. GROUP BY divide las filas en grupos que comparten un valor, y proporciona una fila de resumen por grupo:
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form;
Esto produce una fila para cada form, junto con el recuento y el promedio de esa forma.
Filtering groups with HAVING
WHERE filters rows before grouping. To filter the groups themselves — using an aggregate — use HAVING:
This keeps only forms with 3 or more students. (WHERE cannot test COUNT(*); HAVING can.)
Filtrar grupos con HAVING
WHERE filtra filas antes del agrupamiento. Para filtrar los propios grupos —utilizando un agregado— se usa HAVING:
SELECT form, COUNT(*) AS n
FROM student
GROUP BY form
HAVING COUNT(*) >= 3;
Esto conserva únicamente las formas con 3 o más estudiantes. (WHERE no puede evaluar COUNT(*); HAVING sí puede.)
Common mistakes
- Filter groups with
HAVING, rows withWHERE. - A plain column beside an aggregate must be in
GROUP BY.
Errores comunes
- Filtre los grupos con
HAVING, las filas conWHERE. - Una columna simple junto a un agregado debe estar incluida en
GROUP BY.
For · A favor each · cada form, show the form, the number of students as n, and the average score (2 d.p.) as avg_score. Group by form. · Para cada form, muestra la forma, el número de estudiantes como n, y la puntuación promedio (2 decimales) como avg_score. Agrupa por form.
Click Run to see the output here. · Haz clic en Ejecutar para ver la salida aquí.
Show each form and its student count n, but only for forms with 3 or more students. Use HAVING. · Mostrar cada form y su conteo de estudiantes n, pero solo para formas con 3 o más estudiantes. Usar HAVING.
Click Run to see the output here. · Haz clic en Ejecutar para ver la salida aquí.