Módulo 33: Agrupación y funciones de agregado
Objetivos del módulo
- Usar las funciones de agregado
COUNT,SUM,AVG,MAX,MIN,STDDEV,VARIANCEyMEDIAN, y entender cómo tratan los nulos - Agrupar filas con
GROUP BYpor una o varias columnas y por expresiones - Conocer la regla de las columnas no agregadas y el error
ORA-00979 - Usar
GROUP BYpor alias y por posición (novedad de Oracle 23ai) - Filtrar grupos con
HAVINGy distinguirlo deWHERE - Anidar funciones de agregado (
MAX(AVG(...))) - Generar subtotales y totales con
ROLLUP,CUBEyGROUPING SETS, y etiquetarlos conGROUPINGyGROUPING_ID - Concatenar valores de un grupo con
LISTAGG - Construir tablas cruzadas sencillas con
COUNT/SUMyCASE - Resolver 36 ejercicios de informes sobre el esquema EMPRESA
1. De las filas a los resúmenes
Hasta ahora cada fila del resultado correspondía a una fila de la tabla. Pero muchas preguntas de negocio piden resúmenes: “¿cuántos empleados hay?”, “¿cuál es el salario medio de cada departamento?”, “¿cuántos pedidos se hicieron cada mes?”. Para eso están las funciones de agregado y la cláusula GROUP BY.
📘 Concepto: Una función de agregado (o de grupo, aggregate function) recibe el conjunto de valores de una columna en muchas filas y devuelve un único valor. La cláusula
GROUP BYdivide las filas en grupos (las que comparten el mismo valor en las columnas de agrupación) y aplica las funciones de agregado a cada grupo por separado.
En álgebra relacional (módulo 18) es el operador de agrupación γ:
γ[dept_id; COUNT(*), AVG(salario)](EMPLEADOS)
≡ SELECT dept_id, COUNT(*), AVG(salario) FROM empleados GROUP BY dept_id
flowchart LR
subgraph EMPLEADOS
A["Pedro · 40 · 4200"]
B["Isabel · 40 · 2600"]
C["Jorge · 40 · 1650"]
D["Nuria · 50 · 2300"]
E["Álvaro · 50 · 2700"]
F["Laura · 50 · 4300"]
end
A --> G40["Grupo 40"]
B --> G40
C --> G40
D --> G50["Grupo 50"]
E --> G50
F --> G50
G40 --> R40["40 · COUNT = 3 · SUM = 8450"]
G50 --> R50["50 · COUNT = 3 · SUM = 9300"]
2. Funciones de agregado
2.1. Las funciones básicas
| Función | Devuelve | Tipos | Nulos |
|---|---|---|---|
COUNT(*) | Número de filas | Cualquiera | Cuenta todas las filas |
COUNT(expr) | Número de valores no nulos | Cualquiera | Los ignora |
COUNT(DISTINCT expr) | Número de valores distintos no nulos | Cualquiera | Los ignora |
SUM(expr) | Suma | Numéricos | Los ignora |
AVG(expr) | Media aritmética | Numéricos | Los ignora (¡no cuentan en el divisor!) |
MAX(expr) / MIN(expr) | Máximo / mínimo | Números, textos, fechas | Los ignora |
STDDEV(expr) | Desviación típica (muestral) | Numéricos | Los ignora |
VARIANCE(expr) | Varianza (muestral) | Numéricos | Los ignora |
MEDIAN(expr) | Mediana (valor central) | Numéricos y fechas | Los ignora |
Sin GROUP BY, toda la tabla (las filas que pasan el WHERE) forma un único grupo y el resultado tiene una sola fila:
SELECT COUNT(*) AS empleados,
SUM(salario) AS masa_salarial,
AVG(salario) AS salario_medio,
MIN(salario) AS minimo,
MAX(salario) AS maximo
FROM empleados;
EMPLEADOS MASA_SALARIAL SALARIO_MEDIO MINIMO MAXIMO
---------- ------------- ------------- ---------- ----------
24 67900 2829,16667 900 6500
1 fila seleccionada.
SELECT ROUND(AVG(salario), 2) AS media,
MEDIAN(salario) AS mediana,
ROUND(STDDEV(salario), 2) AS desviacion,
ROUND(VARIANCE(salario), 2) AS varianza
FROM empleados;
MEDIA MEDIANA DESVIACION VARIANZA
---------- ---------- ---------- ----------
2829,17 2450 1339,61 1794547,1
1 fila seleccionada.
La media (2829,17 €) es mayor que la mediana (2450 €) porque unos pocos salarios altos (los directivos) “tiran” de la media hacia arriba; la mediana, el valor que deja la mitad de los empleados por debajo y la mitad por encima, describe mejor el sueldo “típico”. La desviación típica mide cuánto se alejan los salarios de la media.
MAX y MIN también funcionan con textos (orden alfabético) y fechas (orden cronológico):
SELECT MIN(fecha_alta) AS alta_mas_antigua,
MAX(fecha_alta) AS alta_mas_reciente,
MIN(apellidos) AS primer_apellido_alfabetico,
MAX(apellidos) AS ultimo_apellido_alfabetico
FROM empleados;
ALTA_MAS_A ALTA_MAS_R PRIMER_APELLIDO_ALFABETICO ULTIMO_APELLIDO_ALFABETICO
---------- ---------- -------------------------- --------------------------
15/01/2010 01/09/2025 Castro Méndez Vázquez Ramos
1 fila seleccionada.
2.2. COUNT y los nulos: tres formas de contar
SELECT COUNT(*) AS filas,
COUNT(comision) AS con_comision,
COUNT(telefono) AS con_telefono,
COUNT(dept_id) AS con_departamento,
COUNT(DISTINCT dept_id) AS departamentos_distintos,
COUNT(DISTINCT oficio) AS oficios_distintos
FROM empleados;
FILAS CON_COMISION CON_TELEFONO CON_DEPARTAMENTO DEPARTAMENTOS_DISTINTOS OFICIOS_DISTINTOS
---------- ------------ ------------ ---------------- ----------------------- -----------------
24 4 21 23 6 11
1 fila seleccionada.
COUNT(*)cuenta filas: 24 empleados.COUNT(comision)cuenta valores no nulos: solo 4 empleados tienen comisión (incluido el 0 de Sergio, que no es nulo).COUNT(DISTINCT dept_id)cuenta valores distintos no nulos: 6 departamentos con empleados (elNULLde Tomás no cuenta).
⚠️ Importante:
COUNT(*)yCOUNT(columna)no son lo mismo cuando la columna admite nulos. “¿Cuántos empleados hay?” esCOUNT(*); “¿cuántos tienen teléfono?” esCOUNT(telefono).
2.3. AVG y los nulos: la trampa de la media
Todas las funciones de agregado (salvo COUNT(*)) ignoran los nulos. Con SUM, MAX o MIN eso no suele sorprender, pero con AVG cambia el resultado: los nulos no cuentan ni en la suma ni en el divisor.
SELECT SUM(comision) AS suma,
COUNT(comision) AS no_nulos,
COUNT(*) AS empleados,
AVG(comision) AS media_de_los_que_tienen,
AVG(NVL(comision, 0)) AS media_de_todos,
SUM(comision) / COUNT(*) AS comprobacion
FROM empleados;
SUMA NO_NULOS EMPLEADOS MEDIA_DE_LOS_QUE_TIENEN MEDIA_DE_TODOS COMPROBACION
---------- ---------- ---------- ----------------------- -------------- ------------
1600 4 24 400 66,6666667 66,6666667
1 fila seleccionada.
AVG(comision) = 1600 / 4 = 400: es la comisión media de los que tienen comisión. AVG(NVL(comision, 0)) = 1600 / 24 = 66,67: es la comisión media por empleado. Las dos son correctas, pero responden a preguntas distintas. Pregúntate siempre qué significa el nulo en tu columna.
2.4. DISTINCT dentro de las funciones
DISTINCT también puede usarse dentro de SUM y AVG (y de COUNT), para que cada valor distinto cuente una sola vez:
SELECT COUNT(salario) AS salarios,
COUNT(DISTINCT salario) AS salarios_distintos,
SUM(salario) AS suma,
SUM(DISTINCT salario) AS suma_distintos
FROM empleados
WHERE dept_id IN (30, 40, 60);
SALARIOS SALARIOS_DISTINTOS SUMA SUMA_DISTINTOS
---------- ------------------ ---------- --------------
13 11 36200 32000
1 fila seleccionada.
Hay dos salarios de 2600 (Iván e Isabel) y dos de 1600 (Rosa y Manuel); en SUM(DISTINCT salario) cada uno cuenta una vez.
2.5. Agregados sobre un conjunto vacío
Si ninguna fila pasa el WHERE, COUNT devuelve 0 y el resto de funciones devuelven NULL (no 0: la suma de “nada” es desconocida):
SELECT COUNT(*) AS filas, SUM(salario) AS suma, MAX(salario) AS maximo
FROM empleados
WHERE dept_id = 70;
FILAS SUMA MAXIMO
---------- ---------- ----------
0 (nulo) (nulo)
1 fila seleccionada.
💡 Consejo: Si necesitas un 0 en lugar de
NULLen un informe, usaNVL(SUM(salario), 0)oCOALESCE(SUM(salario), 0).
3. GROUP BY: agrupar filas
3.1. Agrupar por una columna
GROUP BY columna forma un grupo por cada valor distinto de la columna y calcula los agregados de cada grupo:
SELECT dept_id,
COUNT(*) AS empleados,
SUM(salario) AS masa_salarial,
ROUND(AVG(salario), 2) AS salario_medio,
MAX(salario) AS maximo
FROM empleados
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID EMPLEADOS MASA_SALARIAL SALARIO_MEDIO MAXIMO
---------- ---------- ------------- ------------- ----------
10 1 6500 6500 6500
20 6 15000 2500 4800
30 6 18800 3133,33 5000
40 3 8450 2816,67 4200
50 3 9300 3100 4300
60 4 8950 2237,5 4000
(nulo) 1 900 900 900
7 filas seleccionadas.
Fíjate en la última fila: los empleados sin departamento (Tomás) forman su propio grupo, el grupo NULL. A efectos de agrupación, todos los nulos se consideran iguales (como en DISTINCT). El departamento 70, que no tiene empleados, no aparece: GROUP BY solo crea grupos con las filas que existen (para incluirlo hará falta un OUTER JOIN, módulo 30).
3.2. Agrupar por varias columnas
Con varias columnas se forma un grupo por cada combinación distinta de valores:
SELECT dept_id, oficio, COUNT(*) AS empleados, SUM(salario) AS total
FROM empleados
WHERE dept_id IN (20, 30, 60)
GROUP BY dept_id, oficio
ORDER BY dept_id, oficio;
DEPT_ID OFICIO EMPLEADOS TOTAL
---------- ---------------- ---------- ----------
20 ADMINISTRATIVO 1 1700
20 DIRECTOR 1 4800
20 VENDEDOR 4 8500
30 ANALISTA 1 3500
30 DIRECTOR 1 5000
30 PROGRAMADOR 3 7800
30 TÉCNICO SISTEMAS 1 2500
60 ADMINISTRATIVO 1 1750
60 DIRECTOR 1 4000
60 OPERARIO 2 3200
10 filas seleccionadas.
3.3. Agrupar por una expresión
Puedes agrupar por cualquier expresión, no solo por columnas. Es la forma de hacer informes por año, por mes o por tramos:
-- Pedidos por año y mes
SELECT TO_CHAR(fecha_pedido, 'YYYY-MM') AS mes,
COUNT(*) AS pedidos
FROM pedidos
GROUP BY TO_CHAR(fecha_pedido, 'YYYY-MM')
ORDER BY mes;
MES PEDIDOS
------- ----------
2025-01 2
2025-02 2
2025-03 2
2025-04 1
2025-05 2
2025-06 2
2025-09 1
2025-10 1
2025-11 2
2026-01 1
...
16 filas seleccionadas.
-- Empleados por año de alta
SELECT EXTRACT(YEAR FROM fecha_alta) AS anio_alta, COUNT(*) AS altas
FROM empleados
GROUP BY EXTRACT(YEAR FROM fecha_alta)
HAVING COUNT(*) > 1
ORDER BY anio_alta;
ANIO_ALTA ALTAS
---------- ----------
2014 2
2016 2
2017 2
2018 2
2019 3
2020 2
2021 2
2022 2
8 filas seleccionadas.
-- Productos por tramo de precio
SELECT CASE
WHEN precio < 100 THEN '1. Menos de 100'
WHEN precio < 500 THEN '2. De 100 a 499'
ELSE '3. 500 o más'
END AS tramo,
COUNT(*) AS productos,
MIN(precio) AS desde,
MAX(precio) AS hasta
FROM productos
GROUP BY CASE
WHEN precio < 100 THEN '1. Menos de 100'
WHEN precio < 500 THEN '2. De 100 a 499'
ELSE '3. 500 o más'
END
ORDER BY tramo;
TRAMO PRODUCTOS DESDE HASTA
--------------- ---------- ---------- ----------
1. Menos de 100 7 19,9 89,9
2. De 100 a 499 8 139 499
3. 500 o más 5 649 1899
3 filas seleccionadas.
3.4. La regla de oro: columnas no agregadas
⚠️ Importante: En una consulta con
GROUP BY(o con funciones de agregado), toda columna o expresión delSELECTque no esté dentro de una función de agregado debe aparecer en elGROUP BY.
La razón es lógica: cada grupo produce una fila. Si agrupas por departamento, el grupo 20 tiene seis empleados con seis nombres distintos; ¿cuál de ellos debería mostrar Oracle en una única fila? No hay respuesta, así que da error:
SELECT dept_id, nombre, COUNT(*)
FROM empleados
GROUP BY dept_id;
SELECT dept_id, nombre, COUNT(*)
*
ERROR en línea 1:
ORA-00979: "NOMBRE": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
El mismo problema aparece si mezclas una columna normal con un agregado sin GROUP BY:
SELECT dept_id, COUNT(*) FROM empleados;
SELECT dept_id, COUNT(*) FROM empleados
*
ERROR en línea 1:
ORA-00937: not a single-group group function
Help: https://docs.oracle.com/error-help/db/ora-00937/
ORA-00937: not a single-group group function significa “has pedido un único grupo (no hay GROUP BY) pero hay una columna que no está agregada”. Las soluciones son añadir la columna al GROUP BY o meterla en una función de agregado (MIN(nombre), MAX(nombre), COUNT(nombre), LISTAGG(nombre)…).
Lo contrario sí está permitido: agrupar por una columna que no se muestra.
SELECT COUNT(*) AS empleados, ROUND(AVG(salario)) AS media
FROM empleados
GROUP BY dept_id
ORDER BY media DESC;
EMPLEADOS MEDIA
---------- ----------
1 6500
6 3133
3 3100
3 2817
6 2500
4 2238
1 900
7 filas seleccionadas.
Es correcto pero poco útil: no sabes a qué departamento corresponde cada fila.
💡 Consejo: Oracle 23ai/26ai (y versiones recientes) incluye la función de agregado
ANY_VALUE(columna), que devuelve un valor cualquiera del grupo. Es útil cuando sabes que todos los valores del grupo son iguales (por ejemplo, el nombre del departamento al agrupar por su código) y no quieres añadirlo alGROUP BY. En versiones antiguas se usaMINoMAXcon el mismo fin.
3.5. GROUP BY por alias y por posición (Oracle 23ai)
Hasta Oracle 21c, en el GROUP BY había que repetir la expresión completa (como en los ejemplos del apartado 3.3), porque el GROUP BY se evalúa antes que el SELECT y el alias todavía no existe. Desde Oracle 23ai puedes usar el alias:
SELECT TO_CHAR(fecha_pedido, 'YYYY') AS anio,
COUNT(*) AS pedidos
FROM pedidos
GROUP BY anio
ORDER BY anio;
ANIO PEDIDOS
---- ----------
2025 15
2026 9
2 filas seleccionadas.
⚠️ Importante: Disponible desde Oracle 23ai. En Oracle 19c y 21c el código anterior da
ORA-00904: "ANIO": invalid identifier; repite la expresión:GROUP BY TO_CHAR(fecha_pedido, 'YYYY').
También se puede agrupar por la posición de la columna en el SELECT, pero esta opción está desactivada por defecto y se activa con un parámetro de sesión:
SELECT dept_id, COUNT(*) AS empleados FROM empleados GROUP BY 1 ORDER BY 1;
SELECT dept_id, COUNT(*) AS empleados FROM empleados GROUP BY 1 ORDER BY 1
*
ERROR en línea 1:
ORA-03162: "DEPT_ID": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE
Help: https://docs.oracle.com/error-help/db/ora-03162/
ALTER SESSION SET group_by_position_enabled = TRUE;
SELECT dept_id, COUNT(*) AS empleados FROM empleados GROUP BY 1 ORDER BY 1;
ALTER SESSION SET group_by_position_enabled = FALSE;
Sesión modificada.
DEPT_ID EMPLEADOS
---------- ----------
10 1
20 6
30 6
40 3
50 3
60 4
(nulo) 1
7 filas seleccionadas.
Sesión modificada.
⚠️ Importante: Disponible desde Oracle 23ai (con
group_by_position_enabled = TRUE). Igual que conORDER BY 1, agrupar por posición es frágil: si cambias el orden de las columnas delSELECT, cambia la agrupación. Úsalo solo en consultas rápidas.
4. HAVING: filtrar grupos
4.1. WHERE frente a HAVING
WHERE filtra filas antes de agrupar; HAVING filtra grupos después de agrupar. Por eso en HAVING se pueden usar funciones de agregado y en WHERE no:
-- ¡Mal! WHERE se evalúa antes de formar los grupos
SELECT dept_id, COUNT(*)
FROM empleados
WHERE COUNT(*) > 3
GROUP BY dept_id;
WHERE COUNT(*) > 3
*
ERROR en línea 3:
ORA-00934: group function is not allowed here
Help: https://docs.oracle.com/error-help/db/ora-00934/
-- Departamentos con más de 3 empleados
SELECT dept_id, COUNT(*) AS empleados
FROM empleados
GROUP BY dept_id
HAVING COUNT(*) > 3
ORDER BY dept_id;
DEPT_ID EMPLEADOS
---------- ----------
20 6
30 6
60 4
3 filas seleccionadas.
4.2. El orden de ejecución completo
flowchart LR
F["FROM<br/>24 empleados"] --> W["WHERE<br/>filtra filas"]
W --> G["GROUP BY<br/>forma grupos"]
G --> H["HAVING<br/>filtra grupos"]
H --> S["SELECT<br/>calcula columnas"]
S --> O["ORDER BY<br/>ordena"]
Un ejemplo que usa todas las cláusulas: “salario medio de los empleados que no son directivos, por departamento, solo de los departamentos donde esa media supere los 2000 €, de mayor a menor”:
SELECT dept_id,
COUNT(*) AS empleados,
ROUND(AVG(salario), 2) AS media
FROM empleados
WHERE oficio NOT LIKE 'DIRECTOR%'
GROUP BY dept_id
HAVING AVG(salario) > 2000
ORDER BY media DESC;
DEPT_ID EMPLEADOS MEDIA
---------- ---------- ----------
30 5 2760
50 2 2500
40 2 2125
20 5 2040
4 filas seleccionadas.
Paso a paso:
| Paso | Cláusula | Qué ocurre |
|---|---|---|
| 1 | FROM empleados | Se parte de los 24 empleados |
| 2 | WHERE oficio NOT LIKE 'DIRECTOR%' | Quedan 18 (se van los 6 directivos) |
| 3 | GROUP BY dept_id | Se forman 6 grupos (20, 30, 40, 50, 60 y NULL) |
| 4 | HAVING AVG(salario) > 2000 | Se descartan los grupos 60 (media 1650) y NULL (900); quedan 4 grupos |
| 5 | SELECT | Se calculan las columnas y los alias |
| 6 | ORDER BY media DESC | Se ordena por el alias |
💡 Consejo: Si una condición se puede poner en
WHERE(no usa agregados), ponla enWHERE: Oracle descarta las filas antes de agruparlas, y eso es más eficiente que agrupar todo y descartar grupos después.HAVINGes solo para condiciones sobre agregados.
4.3. HAVING con condiciones compuestas
HAVING admite cualquier condición sobre los grupos, incluidas varias funciones de agregado:
-- Oficios con al menos 2 empleados y una diferencia entre el máximo y el mínimo salario
SELECT oficio, COUNT(*) AS empleados, MIN(salario) AS minimo, MAX(salario) AS maximo
FROM empleados
GROUP BY oficio
HAVING COUNT(*) >= 2 AND MAX(salario) > MIN(salario)
ORDER BY oficio;
OFICIO EMPLEADOS MINIMO MAXIMO
-------------- ---------- ---------- ----------
ADMINISTRATIVO 3 1650 1750
ANALISTA 2 2700 3500
DIRECTOR 5 4000 5000
PROGRAMADOR 3 2400 2800
VENDEDOR 4 1900 2300
5 filas seleccionadas.
5. Funciones de agregado anidadas
Oracle permite anidar dos niveles de funciones de agregado: la interior se calcula por grupo y la exterior sobre los resultados de todos los grupos. “¿Cuál es el salario medio más alto de todos los departamentos?”:
SELECT MAX(AVG(salario)) AS mayor_media,
MIN(AVG(salario)) AS menor_media,
ROUND(AVG(AVG(salario)), 2) AS media_de_medias
FROM empleados
GROUP BY dept_id;
MAYOR_MEDIA MENOR_MEDIA MEDIA_DE_MEDIAS
----------- ----------- ---------------
6500 900 3026,79
1 fila seleccionada.
La mayor media es 6500 (la del departamento 10, con un único empleado) y la menor 900 (el “grupo” NULL de Tomás). Con un agregado anidado el resultado es una sola fila, así que no puedes mostrar a la vez la columna de agrupación:
SELECT dept_id, MAX(AVG(salario)) FROM empleados GROUP BY dept_id;
SELECT dept_id, MAX(AVG(salario)) FROM empleados GROUP BY dept_id
*
ERROR en línea 1:
ORA-00937: not a single-group group function
Help: https://docs.oracle.com/error-help/db/ora-00937/
📘 Concepto: El anidamiento de agregados es una extensión de Oracle (no existe en el estándar ni en otros SGBD). Para saber qué departamento tiene la mayor media hace falta una subconsulta (módulo 31) o una función analítica (módulo 33).
6. Subtotales y totales: ROLLUP, CUBE y GROUPING SETS
En los informes es habitual querer, además del detalle por grupo, subtotales y un total general. Oracle lo hace en una sola consulta con extensiones de GROUP BY.
6.1. ROLLUP
GROUP BY ROLLUP(a, b) calcula los grupos (a, b), después los subtotales por (a) y finalmente el total general (). Es decir, va “enrollando” de derecha a izquierda:
ROLLUP(a, b) produce | Significado |
|---|---|
(a, b) | Detalle |
(a) | Subtotal de cada a |
() | Total general |
SELECT dept_id, oficio, COUNT(*) AS empleados, SUM(salario) AS total
FROM empleados
WHERE dept_id IN (20, 30)
GROUP BY ROLLUP(dept_id, oficio)
ORDER BY dept_id, oficio;
DEPT_ID OFICIO EMPLEADOS TOTAL
---------- ---------------- ---------- ----------
20 ADMINISTRATIVO 1 1700
20 DIRECTOR 1 4800
20 VENDEDOR 4 8500
20 (nulo) 6 15000
30 ANALISTA 1 3500
30 DIRECTOR 1 5000
30 PROGRAMADOR 3 7800
30 TÉCNICO SISTEMAS 1 2500
30 (nulo) 6 18800
(nulo) (nulo) 12 33800
10 filas seleccionadas.
Las filas de subtotal tienen NULL en las columnas que se han “enrollado”: la fila 20 (nulo) 6 15000 es el subtotal del departamento 20 y la última, con ambas columnas nulas, es el total general.
Con una sola columna, ROLLUP añade simplemente la fila de total:
SELECT estado, COUNT(*) AS pedidos
FROM pedidos
GROUP BY ROLLUP(estado)
ORDER BY estado;
ESTADO PEDIDOS
--------- ----------
CANCELADO 1
ENTREGADO 18
ENVIADO 2
PENDIENTE 3
(nulo) 24
5 filas seleccionadas.
6.2. GROUPING y GROUPING_ID: etiquetar los totales
Hay un problema: ¿cómo distinguir el NULL de “subtotal” del NULL de “dato nulo” (por ejemplo, el departamento de Tomás)? La función GROUPING(columna) devuelve 1 si en esa fila la columna ha sido “enrollada” (es un subtotal) y 0 si es un valor real:
SELECT dept_id, COUNT(*) AS empleados, GROUPING(dept_id) AS es_total
FROM empleados
GROUP BY ROLLUP(dept_id)
ORDER BY GROUPING(dept_id), dept_id;
DEPT_ID EMPLEADOS ES_TOTAL
---------- ---------- ----------
10 1 0
20 6 0
30 6 0
40 3 0
50 3 0
60 4 0
(nulo) 1 0
(nulo) 24 1
8 filas seleccionadas.
Las dos últimas filas tienen dept_id nulo, pero la primera es el grupo de Tomás (GROUPING = 0) y la segunda el total (GROUPING = 1). Con CASE o DECODE podemos poner etiquetas legibles:
SELECT CASE
WHEN GROUPING(dept_id) = 1 THEN 'TOTAL EMPRESA'
WHEN dept_id IS NULL THEN 'Sin departamento'
ELSE TO_CHAR(dept_id)
END AS departamento,
COUNT(*) AS empleados,
SUM(salario) AS masa_salarial
FROM empleados
GROUP BY ROLLUP(dept_id)
ORDER BY GROUPING(dept_id), dept_id;
DEPARTAMENTO EMPLEADOS MASA_SALARIAL
---------------- ---------- -------------
10 1 6500
20 6 15000
30 6 18800
40 3 8450
50 3 9300
60 4 8950
Sin departamento 1 900
TOTAL EMPRESA 24 67900
8 filas seleccionadas.
⚠️ Importante: Desde Oracle 23ai, al poder usar alias en
GROUP BYy en las expresiones deORDER BY, un alias con el mismo nombre que una columna puede “taparla”. En la consulta siguiente,GROUPING(estado)delORDER BYse interpreta comoGROUPINGdel aliasestado(es decir, deNVL(estado, 'TOTAL')), que no es una expresión de agrupación:
SELECT NVL(estado, 'TOTAL') AS estado, COUNT(*) AS pedidos
FROM pedidos
GROUP BY ROLLUP(estado)
ORDER BY GROUPING(estado);
SELECT NVL(estado, 'TOTAL') AS estado, COUNT(*) AS pedidos
*
ERROR en línea 1:
ORA-00979: NVL(NVL("PEDIDOS"."ESTADO",'TOTAL'),'TOTAL'): must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
La solución es sencilla: no reutilices nombres de columna como alias de expresiones distintas (aquí bastaría con AS estado_pedido). Es una buena práctica en cualquier versión.
GROUPING_ID(a, b, ...) combina en un número los GROUPING de varias columnas, leídos como bits: con dos columnas vale 0 para el detalle, 1 para los subtotales del primer nivel, 2 para los del segundo y 3 para el total general. Es muy útil para ordenar o filtrar niveles de un informe:
SELECT DECODE(GROUPING_ID(dept_id, oficio),
0, 'Detalle',
1, 'Subtotal dept.',
3, 'TOTAL') AS nivel,
dept_id,
oficio,
COUNT(*) AS empleados,
SUM(salario) AS total,
GROUPING(dept_id) AS g_dept,
GROUPING(oficio) AS g_oficio,
GROUPING_ID(dept_id, oficio) AS gid
FROM empleados
WHERE dept_id IN (40, 60)
GROUP BY ROLLUP(dept_id, oficio)
ORDER BY dept_id, GROUPING_ID(dept_id, oficio), oficio;
NIVEL DEPT_ID OFICIO EMPLEADOS TOTAL G_DEPT G_OFICIO GID
-------------- ---------- -------------- ---------- ---------- ---------- ---------- ----------
Detalle 40 ADMINISTRATIVO 1 1650 0 0 0
Detalle 40 CONTABLE 1 2600 0 0 0
Detalle 40 DIRECTOR 1 4200 0 0 0
Subtotal dept. 40 (nulo) 3 8450 0 1 1
Detalle 60 ADMINISTRATIVO 1 1750 0 0 0
Detalle 60 DIRECTOR 1 4000 0 0 0
Detalle 60 OPERARIO 2 3200 0 0 0
Subtotal dept. 60 (nulo) 4 8950 0 1 1
TOTAL (nulo) (nulo) 7 17400 1 1 3
9 filas seleccionadas.
GROUPING(dept_id) | GROUPING(oficio) | GROUPING_ID (binario) | Nivel |
|---|---|---|---|
| 0 | 0 | 0 (00) | Detalle departamento + oficio |
| 0 | 1 | 1 (01) | Subtotal por departamento |
| 1 | 0 | 2 (10) | Subtotal por oficio (solo con CUBE) |
| 1 | 1 | 3 (11) | Total general |
6.3. CUBE
GROUP BY CUBE(a, b) calcula todas las combinaciones posibles de subtotales: (a, b), (a), (b) y (). Con n columnas genera 2ⁿ agrupaciones. Es la base de los informes “de tabla dinámica”:
SELECT dept_id, oficio, COUNT(*) AS empleados, GROUPING_ID(dept_id, oficio) AS gid
FROM empleados
WHERE dept_id IN (20, 40)
GROUP BY CUBE(dept_id, oficio)
ORDER BY GROUPING_ID(dept_id, oficio), dept_id, oficio;
DEPT_ID OFICIO EMPLEADOS GID
---------- -------------- ---------- ----------
20 ADMINISTRATIVO 1 0
20 DIRECTOR 1 0
20 VENDEDOR 4 0
40 ADMINISTRATIVO 1 0
40 CONTABLE 1 0
40 DIRECTOR 1 0
20 (nulo) 6 1
40 (nulo) 3 1
(nulo) ADMINISTRATIVO 2 2
(nulo) CONTABLE 1 2
(nulo) DIRECTOR 2 2
(nulo) VENDEDOR 4 2
(nulo) (nulo) 9 3
13 filas seleccionadas.
Respecto a ROLLUP, aparecen las filas con gid = 2: subtotales por oficio para todos los departamentos (por ejemplo, 2 administrativos entre el 20 y el 40).
6.4. GROUPING SETS
GROUPING SETS permite indicar exactamente qué agrupaciones quieres, sin generar las demás. Cada conjunto entre paréntesis es una agrupación; () es el total:
SELECT dept_id, oficio, COUNT(*) AS empleados
FROM empleados
WHERE dept_id IN (20, 40)
GROUP BY GROUPING SETS ((dept_id), (oficio), ())
ORDER BY dept_id, oficio;
DEPT_ID OFICIO EMPLEADOS
---------- -------------- ----------
20 (nulo) 6
40 (nulo) 3
(nulo) ADMINISTRATIVO 2
(nulo) CONTABLE 1
(nulo) DIRECTOR 2
(nulo) VENDEDOR 4
(nulo) (nulo) 9
7 filas seleccionadas.
| Extensión | Equivale a GROUPING SETS |
|---|---|
ROLLUP(a, b) | ((a, b), (a), ()) |
ROLLUP(a, b, c) | ((a, b, c), (a, b), (a), ()) |
CUBE(a, b) | ((a, b), (a), (b), ()) |
GROUP BY a, ROLLUP(b) | ((a, b), (a)) |
💡 Consejo:
GROUPING SETSes lo mismo que hacer varias consultas conGROUP BYdistintos y unirlas conUNION ALL(módulo 32), pero en una sola lectura de la tabla y con mucho menos código.
7. LISTAGG: concatenar los valores de un grupo
LISTAGG(expr, separador) WITHIN GROUP (ORDER BY ...) es una función de agregado que concatena en una sola cadena los valores del grupo, en el orden indicado:
SELECT dept_id,
COUNT(*) AS empleados,
LISTAGG(nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS plantilla
FROM empleados
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID EMPLEADOS PLANTILLA
---------- ---------- -----------------------------------------
10 1 Ana
20 6 Carmen, David, Elena, Lucía, Luis, Sergio
30 6 Hugo, Iván, Marta, Pablo, Raquel, Sara
40 3 Isabel, Jorge, Pedro
50 3 Álvaro, Laura, Nuria
60 4 Cristina, Javier, Manuel, Rosa
(nulo) 1 Tomás
7 filas seleccionadas.
LISTAGG(DISTINCT ...) elimina los repetidos (desde Oracle 19c):
SELECT dept_id,
LISTAGG(oficio, ' / ') WITHIN GROUP (ORDER BY oficio) AS con_repetidos,
LISTAGG(DISTINCT oficio, ' / ') WITHIN GROUP (ORDER BY oficio) AS sin_repetidos
FROM empleados
WHERE dept_id IN (20, 30)
GROUP BY dept_id;
DEPT_ID CON_REPETIDOS SIN_REPETIDOS
---------- -------------------------------------------------------------------------------- ----------------------------------------------------
20 ADMINISTRATIVO / DIRECTOR / VENDEDOR / VENDEDOR / VENDEDOR / VENDEDOR ADMINISTRATIVO / DIRECTOR / VENDEDOR
30 ANALISTA / DIRECTOR / PROGRAMADOR / PROGRAMADOR / PROGRAMADOR / TÉCNICO SISTEMAS ANALISTA / DIRECTOR / PROGRAMADOR / TÉCNICO SISTEMAS
2 filas seleccionadas.
El resultado de LISTAGG es un VARCHAR2 con un máximo de 4000 bytes (32767 si la base de datos tiene MAX_STRING_SIZE = EXTENDED). Si la lista no cabe, se produce un error:
SELECT LENGTH(LISTAGG(RPAD(nombre, 200, '.'), ', ') WITHIN GROUP (ORDER BY nombre)) AS longitud
FROM empleados;
SELECT LENGTH(LISTAGG(RPAD(nombre, 200, '.'), ', ') WITHIN GROUP (ORDER BY nombre)) AS longitud
*
ERROR en línea 1:
ORA-01489: result of string concatenation is too long
Help: https://docs.oracle.com/error-help/db/ora-01489/
Desde Oracle 12.2, la cláusula ON OVERFLOW TRUNCATE corta la lista, añade un indicador ('...' por defecto) y, con WITH COUNT, el número de elementos que se han quedado fuera:
SELECT LENGTH(LISTAGG(RPAD(nombre, 200, '.'), ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
WITHIN GROUP (ORDER BY nombre)) AS longitud,
SUBSTR(LISTAGG(RPAD(nombre, 200, '.'), ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
WITHIN GROUP (ORDER BY nombre), -20) AS final_de_la_lista
FROM empleados;
LONGITUD FINAL_DE_LA_LISTA
---------- --------------------
3844 ............, ...(5)
1 fila seleccionada.
Cada nombre se ha rellenado hasta 200 caracteres para provocar el desbordamiento: caben 19 de los 24 y el final de la cadena indica ...(5).
| Opción | Efecto |
|---|---|
ON OVERFLOW ERROR | Error ORA-01489 (comportamiento por defecto) |
ON OVERFLOW TRUNCATE | Corta y añade ... |
ON OVERFLOW TRUNCATE 'texto' | Corta y añade el texto indicado |
... WITH COUNT / WITHOUT COUNT | Añade o no (n) con los elementos omitidos (por defecto, WITH COUNT) |
8. Tablas cruzadas con agregados condicionales
Combinando COUNT o SUM con CASE se pueden “girar” los datos: cada valor de una columna se convierte en una columna del informe. Es la técnica clásica para hacer tablas cruzadas (pivot) sin el operador PIVOT (módulo 33).
-- Número de pedidos de cada estado por año
SELECT EXTRACT(YEAR FROM fecha_pedido) AS anio,
COUNT(*) AS total,
COUNT(CASE WHEN estado = 'ENTREGADO' THEN 1 END) AS entregados,
COUNT(CASE WHEN estado = 'ENVIADO' THEN 1 END) AS enviados,
COUNT(CASE WHEN estado = 'PENDIENTE' THEN 1 END) AS pendientes,
COUNT(CASE WHEN estado = 'CANCELADO' THEN 1 END) AS cancelados
FROM pedidos
GROUP BY EXTRACT(YEAR FROM fecha_pedido)
ORDER BY anio;
ANIO TOTAL ENTREGADOS ENVIADOS PENDIENTES CANCELADOS
---------- ---------- ---------- ---------- ---------- ----------
2025 15 14 0 0 1
2026 9 4 2 3 0
2 filas seleccionadas.
El truco: CASE WHEN estado = 'ENTREGADO' THEN 1 END vale 1 para los entregados y NULL para el resto (no hay ELSE), y COUNT solo cuenta los no nulos. Con SUM se suman importes condicionados:
-- Masa salarial por departamento, separando directivos y resto
SELECT dept_id,
SUM(salario) AS total,
SUM(CASE WHEN oficio LIKE 'DIRECTOR%' THEN salario ELSE 0 END) AS directivos,
SUM(CASE WHEN oficio NOT LIKE 'DIRECTOR%' THEN salario ELSE 0 END) AS resto,
ROUND(100 * SUM(CASE WHEN oficio LIKE 'DIRECTOR%' THEN salario ELSE 0 END)
/ SUM(salario), 1) AS pct_directivos
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID TOTAL DIRECTIVOS RESTO PCT_DIRECTIVOS
---------- ---------- ---------- ---------- --------------
10 6500 6500 0 100
20 15000 4800 10200 32
30 18800 5000 13800 26,6
40 8450 4200 4250 49,7
50 9300 4300 5000 46,2
60 8950 4000 4950 44,7
6 filas seleccionadas.
9. Errores frecuentes
| Error | Causa | Solución |
|---|---|---|
ORA-00979: ... must appear in the GROUP BY clause or be used in an aggregate function | Columna del SELECT (o del ORDER BY) que no está en el GROUP BY ni agregada | Añádela al GROUP BY o agrégala |
ORA-00937: not a single-group group function | Columna normal junto a un agregado sin GROUP BY, o agregado anidado con la columna de grupo | Añade GROUP BY o quita la columna |
ORA-00934: group function is not allowed here | Agregado en WHERE | Usa HAVING |
ORA-00904 al agrupar por alias | Versión anterior a 23ai | Repite la expresión en el GROUP BY |
ORA-03162 al agrupar por posición | group_by_position_enabled es FALSE | Actívalo o usa el nombre de la columna |
ORA-01489: result of string concatenation is too long | LISTAGG supera 4000 bytes | ON OVERFLOW TRUNCATE |
| Media “demasiado alta” | AVG ignora los nulos | AVG(NVL(col, 0)) si el nulo significa 0 |
| Falta un grupo esperado (p. ej. el departamento 70) | GROUP BY solo agrupa filas existentes | OUTER JOIN (módulo 30) |
| Total que no cuadra | COUNT(col) en lugar de COUNT(*) | Elige el COUNT adecuado |
10. Ejercicios
A continuación tienes 36 ejercicios resueltos de informes sobre EMPRESA y 15 propuestos. Como aún no sabes combinar tablas, todos trabajan sobre una sola tabla; en el módulo 30 podrás añadir los nombres de clientes, productos o departamentos a estos informes.
Ejercicio 1: Plantilla total
¿Cuántos empleados hay y cuántos tienen teléfono registrado?
Ver solución
SELECT COUNT(*) AS empleados, COUNT(telefono) AS con_telefono
FROM empleados;
EMPLEADOS CON_TELEFONO
---------- ------------
24 21
1 fila seleccionada.
Ejercicio 2: Estadísticas de precios
Muestra el número de productos, el precio mínimo, el máximo, el medio (redondeado a 2 decimales) y la mediana del catálogo no descatalogado.
Ver solución
SELECT COUNT(*) AS productos,
MIN(precio) AS minimo,
MAX(precio) AS maximo,
ROUND(AVG(precio), 2) AS media,
MEDIAN(precio) AS mediana
FROM productos
WHERE descatalogado = 'N';
PRODUCTOS MINIMO MAXIMO MEDIA MEDIANA
---------- ---------- ---------- ---------- ----------
19 19,9 1899 455,58 279
1 fila seleccionada.
Ejercicio 3: Valor del inventario
Calcula el valor total del inventario (precio × stock de todos los productos) y el número total de unidades en almacén.
Ver solución
SELECT SUM(precio * stock) AS valor_inventario,
SUM(stock) AS unidades
FROM productos;
VALOR_INVENTARIO UNIDADES
---------------- ----------
140701 688
1 fila seleccionada.
Las funciones de agregado admiten expresiones: primero se calcula precio * stock en cada fila y después se suman los resultados.
Ejercicio 4: Clientes con email
¿Cuántos clientes hay, cuántos tienen email, cuántos tienen comercial asignado y en cuántas provincias distintas hay clientes?
Ver solución
SELECT COUNT(*) AS clientes,
COUNT(email) AS con_email,
COUNT(comercial_id) AS con_comercial,
COUNT(DISTINCT provincia) AS provincias
FROM clientes;
CLIENTES CON_EMAIL CON_COMERCIAL PROVINCIAS
---------- ---------- ------------- ----------
14 12 12 11
1 fila seleccionada.
Ejercicio 5: Primer y último pedido
Muestra la fecha del primer y del último pedido, y cuántos días hay entre ambos.
Ver solución
SELECT MIN(fecha_pedido) AS primero,
MAX(fecha_pedido) AS ultimo,
MAX(fecha_pedido) - MIN(fecha_pedido) AS dias
FROM pedidos;
PRIMERO ULTIMO DIAS
---------- ---------- ----------
15/01/2025 10/07/2026 541
1 fila seleccionada.
Ejercicio 6: Empleados por departamento
Muestra el número de empleados de cada departamento, del que más tiene al que menos.
Ver solución
SELECT dept_id, COUNT(*) AS empleados
FROM empleados
GROUP BY dept_id
ORDER BY empleados DESC, dept_id;
DEPT_ID EMPLEADOS
---------- ----------
20 6
30 6
60 4
40 3
50 3
10 1
(nulo) 1
7 filas seleccionadas.
Ejercicio 7: Salarios por departamento
Para cada departamento, muestra la masa salarial, el salario medio redondeado a 2 decimales, el mínimo y el máximo. Excluye a los empleados sin departamento.
Ver solución
SELECT dept_id,
SUM(salario) AS masa_salarial,
ROUND(AVG(salario), 2) AS media,
MIN(salario) AS minimo,
MAX(salario) AS maximo
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID MASA_SALARIAL MEDIA MINIMO MAXIMO
---------- ------------- ---------- ---------- ----------
10 6500 6500 6500 6500
20 15000 2500 1700 4800
30 18800 3133,33 2400 5000
40 8450 2816,67 1650 4200
50 9300 3100 2300 4300
60 8950 2237,5 1600 4000
6 filas seleccionadas.
Ejercicio 8: Empleados por oficio
Muestra cuántos empleados hay de cada oficio y su salario medio, ordenado por número de empleados.
Ver solución
SELECT oficio, COUNT(*) AS empleados, ROUND(AVG(salario), 2) AS media
FROM empleados
GROUP BY oficio
ORDER BY empleados DESC, oficio;
OFICIO EMPLEADOS MEDIA
---------------- ---------- ----------
DIRECTOR 5 4460
VENDEDOR 4 2125
ADMINISTRATIVO 3 1700
PROGRAMADOR 3 2600
ANALISTA 2 3100
OPERARIO 2 1600
BECARIO 1 900
CONTABLE 1 2600
DIRECTOR GENERAL 1 6500
DISEÑADOR 1 2300
TÉCNICO SISTEMAS 1 2500
11 filas seleccionadas.
Ejercicio 9: Clientes por provincia
Muestra el número de clientes y el límite de crédito total de cada provincia, solo de las provincias con más de un cliente.
Ver solución
SELECT provincia, COUNT(*) AS clientes, SUM(limite_credito) AS credito_total
FROM clientes
GROUP BY provincia
HAVING COUNT(*) > 1
ORDER BY clientes DESC, provincia;
PROVINCIA CLIENTES CREDITO_TOTAL
--------- ---------- -------------
Madrid 2 10000
Sevilla 2 7500
Valencia 2 11500
3 filas seleccionadas.
Ejercicio 10: Cartera de cada comercial
Muestra, para cada comercial, cuántos clientes tiene asignados y el crédito total que gestiona. Los clientes sin comercial deben aparecer como un grupo aparte.
Ver solución
SELECT comercial_id, COUNT(*) AS clientes, SUM(limite_credito) AS credito
FROM clientes
GROUP BY comercial_id
ORDER BY comercial_id;
COMERCIAL_ID CLIENTES CREDITO
------------ ---------- ----------
110 3 42000
111 3 25000
112 3 27500
113 3 11500
(nulo) 2 2500
5 filas seleccionadas.
La última fila (comercial_id nulo) agrupa a los dos clientes sin comercial.
Ejercicio 11: Pedidos por cliente
Muestra cuántos pedidos ha hecho cada cliente y la fecha de su último pedido, ordenado por número de pedidos.
Ver solución
SELECT cliente_id, COUNT(*) AS pedidos, MAX(fecha_pedido) AS ultimo_pedido
FROM pedidos
GROUP BY cliente_id
ORDER BY pedidos DESC, cliente_id;
CLIENTE_ID PEDIDOS ULTIMO_PED
---------- ---------- ----------
1001 3 15/06/2026
1002 3 03/03/2026
1003 2 27/11/2025
1004 2 08/09/2025
1005 2 21/04/2026
1007 2 30/05/2026
1008 2 10/07/2026
1009 2 08/07/2026
1010 2 12/01/2026
1012 2 01/07/2026
1006 1 25/06/2025
1011 1 09/02/2026
12 filas seleccionadas.
Los clientes 1013 y 1014 no aparecen porque no tienen pedidos: no hay filas suyas en PEDIDOS que agrupar.
Ejercicio 12: Clientes fieles
Muestra los clientes con al menos 3 pedidos.
Ver solución
SELECT cliente_id, COUNT(*) AS pedidos
FROM pedidos
GROUP BY cliente_id
HAVING COUNT(*) >= 3
ORDER BY pedidos DESC;
CLIENTE_ID PEDIDOS
---------- ----------
1002 3
1001 3
2 filas seleccionadas.
Ejercicio 13: Pedidos gestionados por cada empleado
Muestra cuántos pedidos ha gestionado cada empleado, excluyendo los pedidos cancelados. Los pedidos online (sin empleado) deben aparecer con la etiqueta ONLINE.
Ver solución
SELECT NVL(TO_CHAR(empleado_id), 'ONLINE') AS empleado, COUNT(*) AS pedidos
FROM pedidos
WHERE estado <> 'CANCELADO'
GROUP BY empleado_id
ORDER BY pedidos DESC;
EMPLEADO PEDIDOS
-------- ----------
110 8
111 6
112 5
ONLINE 2
113 2
5 filas seleccionadas.
NVL(TO_CHAR(empleado_id), ...) puede ir en el SELECT aunque no esté literalmente en el GROUP BY, porque es una función de una columna que sí está agrupada: dentro de cada grupo empleado_id tiene un único valor.
Ejercicio 14: Pedidos por estado
Muestra cuántos pedidos hay en cada estado, con una fila final de total.
Ver solución
SELECT NVL(estado, 'TOTAL') AS estado_pedido, COUNT(*) AS pedidos
FROM pedidos
GROUP BY ROLLUP(estado)
ORDER BY GROUPING(estado), pedidos DESC;
ESTADO_PEDID PEDIDOS
------------ ----------
ENTREGADO 18
PENDIENTE 3
ENVIADO 2
CANCELADO 1
TOTAL 24
5 filas seleccionadas.
Aquí basta NVL para etiquetar el total porque estado es NOT NULL: el único nulo posible es el del ROLLUP. Si la columna admitiera nulos, habría que usar GROUPING. Fíjate en que el alias (estado_pedido) es distinto del nombre de la columna, por lo que se explica en el apartado 6.2.
Ejercicio 15: Ventas mensuales
Muestra el número de pedidos de cada mes (formato AAAA-MM) de 2025, sin contar los cancelados.
Ver solución
SELECT TO_CHAR(fecha_pedido, 'YYYY-MM') AS mes, COUNT(*) AS pedidos
FROM pedidos
WHERE fecha_pedido >= DATE '2025-01-01'
AND fecha_pedido < DATE '2026-01-01'
AND estado <> 'CANCELADO'
GROUP BY TO_CHAR(fecha_pedido, 'YYYY-MM')
ORDER BY mes;
MES PEDIDOS
------- ----------
2025-01 2
2025-02 2
2025-03 2
2025-04 1
2025-05 1
2025-06 2
2025-09 1
2025-10 1
2025-11 2
9 filas seleccionadas.
Los meses sin pedidos (julio, agosto y diciembre) no aparecen, y mayo solo cuenta uno porque el otro pedido de ese mes está cancelado: para mostrarlos con 0 haría falta generar la lista de meses y hacer un OUTER JOIN (módulos 30 y 32).
Ejercicio 16: Pedidos por trimestre
Muestra el número de pedidos por año y trimestre.
Ver solución
SELECT EXTRACT(YEAR FROM fecha_pedido) AS anio,
TO_CHAR(fecha_pedido, 'Q') AS trimestre,
COUNT(*) AS pedidos
FROM pedidos
GROUP BY EXTRACT(YEAR FROM fecha_pedido), TO_CHAR(fecha_pedido, 'Q')
ORDER BY anio, trimestre;
ANIO T PEDIDOS
---------- - ----------
2025 1 6
2025 2 5
2025 3 1
2025 4 3
2026 1 3
2026 2 3
2026 3 3
7 filas seleccionadas.
Ejercicio 17: Día de la semana con más pedidos
Muestra cuántos pedidos se han hecho cada día de la semana, ordenados de lunes a domingo.
Ver solución
SELECT TO_CHAR(fecha_pedido, 'D') AS num,
TO_CHAR(fecha_pedido, 'fmDay') AS dia,
COUNT(*) AS pedidos
FROM pedidos
GROUP BY TO_CHAR(fecha_pedido, 'D'), TO_CHAR(fecha_pedido, 'fmDay')
ORDER BY num;
N DIA PEDIDOS
- --------- ----------
1 Lunes 8
2 Martes 5
3 Miércoles 5
4 Jueves 3
5 Viernes 1
6 Sábado 2
6 filas seleccionadas.
Se agrupa por el número (para ordenar de lunes a domingo) y por el nombre (para mostrarlo). Como ambos dependen solo del día de la semana, no se crean grupos de más.
Ejercicio 18: Importe de cada pedido
A partir de LINEAS_PEDIDO, calcula el importe neto de cada pedido (cantidad × precio_unitario × (1 − descuento / 100)), redondeado a 2 decimales, y su número de líneas. Muestra los 5 pedidos de mayor importe.
Ver solución
SELECT pedido_id,
COUNT(*) AS lineas,
ROUND(SUM(cantidad * precio_unitario * (1 - descuento / 100)), 2) AS importe
FROM lineas_pedido
GROUP BY pedido_id
ORDER BY importe DESC
FETCH FIRST 5 ROWS ONLY;
PEDIDO_ID LINEAS IMPORTE
---------- ---------- ----------
5010 4 11657,5
5016 2 8945,05
5002 3 7402,5
5004 3 6232,5
5009 3 4772,44
5 filas seleccionadas.
Ejercicio 19: Pedidos grandes
Muestra los pedidos cuyo importe neto supera los 2000 €, con el importe bruto (sin descuentos), el neto y el ahorro por descuentos.
Ver solución
SELECT pedido_id,
SUM(cantidad * precio_unitario) AS bruto,
ROUND(SUM(cantidad * precio_unitario * (1 - descuento / 100)), 2) AS neto,
ROUND(SUM(cantidad * precio_unitario * descuento / 100), 2) AS ahorro
FROM lineas_pedido
GROUP BY pedido_id
HAVING SUM(cantidad * precio_unitario * (1 - descuento / 100)) > 2000
ORDER BY neto DESC;
PEDIDO_ID BRUTO NETO AHORRO
---------- ---------- ---------- ----------
5010 13675 11657,5 2017,5
5016 9889 8945,05 943,95
5002 8225 7402,5 822,5
5004 6539,5 6232,5 307
5009 5294 4772,44 521,56
5007 2711,4 2539,13 172,27
5021 2557 2462,05 94,95
5018 2195 2085,25 109,75
8 filas seleccionadas.
En el HAVING se repite la expresión sin redondear; desde 23ai también podrías escribir HAVING neto > 2000.
Ejercicio 20: Productos más vendidos
Muestra los 5 productos (por su código) de los que se han vendido más unidades en total, con el número de pedidos en que aparecen.
Ver solución
SELECT producto_id,
SUM(cantidad) AS unidades,
COUNT(DISTINCT pedido_id) AS pedidos
FROM lineas_pedido
GROUP BY producto_id
ORDER BY unidades DESC, producto_id
FETCH FIRST 5 ROWS ONLY;
PRODUCTO_ID UNIDADES PEDIDOS
----------- ---------- ----------
9 40 4
8 30 2
3 24 3
1 15 2
7 15 3
5 filas seleccionadas.
Ejercicio 21: Descuento medio por producto
Muestra para cada producto vendido el descuento medio aplicado y el máximo, solo de los productos con algún descuento.
Ver solución
SELECT producto_id,
ROUND(AVG(descuento), 2) AS descuento_medio,
MAX(descuento) AS descuento_maximo,
COUNT(*) AS veces_vendido
FROM lineas_pedido
GROUP BY producto_id
HAVING MAX(descuento) > 0
ORDER BY descuento_medio DESC;
PRODUCTO_ID DESCUENTO_MEDIO DESCUENTO_MAXIMO VECES_VENDIDO
----------- --------------- ---------------- -------------
8 12,5 15 2
9 8,75 15 4
5 8,5 12 2
1 7,5 10 2
4 5 10 2
3 5 15 3
11 4,33 8 3
12 2,5 5 2
10 2,5 5 4
19 2,5 5 2
13 2 8 4
7 1,67 5 3
12 filas seleccionadas.
Ejercicio 22: Productos por categoría
Muestra cuántos productos hay en cada categoría, su precio medio y el stock total.
Ver solución
SELECT categoria_id,
COUNT(*) AS productos,
ROUND(AVG(precio), 2) AS precio_medio,
SUM(stock) AS stock_total
FROM productos
GROUP BY categoria_id
ORDER BY categoria_id;
CATEGORIA_ID PRODUCTOS PRECIO_MEDIO STOCK_TOTAL
------------ ---------- ------------ -----------
1 1 89,9 70
2 3 965,67 75
3 2 1274 19
4 2 117 39
5 3 44,77 260
6 2 234 47
7 1 279 14
8 2 649 58
9 2 54,45 80
11 2 324 26
10 filas seleccionadas.
Ejercicio 23: Valor del inventario por categoría
Muestra el valor del inventario (precio × stock) por categoría, solo de las categorías cuyo inventario vale más de 10 000 €, de mayor a menor.
Ver solución
SELECT categoria_id, SUM(precio * stock) AS valor
FROM productos
GROUP BY categoria_id
HAVING SUM(precio * stock) > 10000
ORDER BY valor DESC;
CATEGORIA_ID VALOR
------------ ----------
2 57425
8 22942
3 17331
3 filas seleccionadas.
Ejercicio 24: Horas por proyecto
A partir de ASIGNACIONES, muestra para cada proyecto el número de empleados asignados, las horas totales y las horas medias por empleado.
Ver solución
SELECT proyecto_id,
COUNT(*) AS empleados,
SUM(horas) AS horas_totales,
ROUND(AVG(horas), 1) AS horas_medias
FROM asignaciones
GROUP BY proyecto_id
ORDER BY proyecto_id;
PROYECTO_ID EMPLEADOS HORAS_TOTALES HORAS_MEDIAS
----------- ---------- ------------- ------------
1 3 1430 476,7
2 4 1350 337,5
3 4 480 120
4 3 300 100
4 filas seleccionadas.
El proyecto 5 no aparece porque no tiene asignaciones.
Ejercicio 25: Empleados en varios proyectos
Muestra los empleados que participan en más de un proyecto, con la lista de proyectos.
Ver solución
SELECT emp_id,
COUNT(*) AS proyectos,
LISTAGG(proyecto_id, ', ') WITHIN GROUP (ORDER BY proyecto_id) AS lista,
SUM(horas) AS horas
FROM asignaciones
GROUP BY emp_id
HAVING COUNT(*) > 1;
EMP_ID PROYECTOS LISTA HORAS
---------- ---------- ----- ----------
120 2 1, 2 750
140 2 2, 3 320
2 filas seleccionadas.
Ejercicio 26: Media de comisiones
Calcula la comisión media de los vendedores de dos formas: entre los que tienen comisión y entre todos los vendedores (considerando 0 las comisiones nulas). ¿Por qué coinciden en este caso?
Ver solución
SELECT AVG(comision) AS media_con_comision,
AVG(NVL(comision, 0)) AS media_todos,
COUNT(*) AS vendedores,
COUNT(comision) AS con_comision
FROM empleados
WHERE oficio = 'VENDEDOR';
MEDIA_CON_COMISION MEDIA_TODOS VENDEDORES CON_COMISION
------------------ ----------- ---------- ------------
400 400 4 4
1 fila seleccionada.
Coinciden porque los cuatro vendedores tienen comisión no nula (la de Sergio es 0, pero no es NULL), así que COUNT(comision) = COUNT(*). Repite la consulta para todo el departamento 20 y verás cómo difieren.
Ejercicio 27: Antigüedad media por departamento
Muestra para cada departamento la antigüedad media de sus empleados en años (con un decimal) y la fecha de alta del más antiguo.
Ver solución
SELECT dept_id,
ROUND(AVG(MONTHS_BETWEEN(SYSDATE, fecha_alta) / 12), 1) AS antiguedad_media,
MIN(fecha_alta) AS alta_mas_antigua
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY antiguedad_media DESC;
DEPT_ID ANTIGUEDAD_MEDIA ALTA_MAS_A
---------- ---------------- ----------
10 16,7 15/01/2010
20 9 01/03/2012
40 9 10/09/2013
30 8,9 20/06/2011
50 8,5 02/02/2015
60 8,2 11/11/2014
6 filas seleccionadas.
Ejercicio 28: Plantilla en una línea
Muestra, para cada oficio con más de un empleado, la lista de los apellidos de sus empleados separados por ; y ordenados alfabéticamente.
Ver solución
SELECT oficio,
LISTAGG(apellidos, '; ') WITHIN GROUP (ORDER BY apellidos) AS empleados
FROM empleados
GROUP BY oficio
HAVING COUNT(*) > 1
ORDER BY oficio;
OFICIO EMPLEADOS
-------------- -----------------------------------------------------------------------
ADMINISTRATIVO Medina Blanco; Romero Serrano; Vázquez Ramos
ANALISTA Moreno Herrera; Peña Cano
DIRECTOR Fernández Moreno; Gómez Díaz; López Navarro; Martínez Ruiz; Sánchez Gil
OPERARIO Herrero Lozano; Iglesias Cruz
PROGRAMADOR Castro Méndez; Molina Prieto; Ortiz Delgado
VENDEDOR Jiménez Vidal; Navarro Castro; Ruiz Ortega; Torres Romero
6 filas seleccionadas.
Ejercicio 29: Ciudades de cada comercial
Muestra, para cada comercial con clientes, las ciudades distintas de sus clientes en una sola columna.
Ver solución
SELECT comercial_id,
COUNT(*) AS clientes,
LISTAGG(DISTINCT ciudad, ', ') WITHIN GROUP (ORDER BY ciudad) AS ciudades
FROM clientes
WHERE comercial_id IS NOT NULL
GROUP BY comercial_id
ORDER BY comercial_id;
COMERCIAL_ID CLIENTES CIUDADES
------------ ---------- -----------------------------
110 3 Alicante, Barcelona, Valencia
111 3 Madrid, Palma
112 3 Granada, Sevilla
113 3 Oviedo, Valladolid, Zaragoza
4 filas seleccionadas.
Ejercicio 30: Mayor media salarial
¿Cuál es la mayor media salarial por oficio, y cuál la menor?
Ver solución
SELECT MAX(AVG(salario)) AS mayor_media,
MIN(AVG(salario)) AS menor_media,
MAX(AVG(salario)) - MIN(AVG(salario)) AS diferencia
FROM empleados
GROUP BY oficio;
MAYOR_MEDIA MENOR_MEDIA DIFERENCIA
----------- ----------- ----------
6500 900 5600
1 fila seleccionada.
Ejercicio 31: Informe de salarios con subtotales
Muestra la masa salarial por departamento y oficio de los departamentos 30 y 50, con subtotales por departamento y total general, etiquetando las filas de total.
Ver solución
SELECT CASE GROUPING_ID(dept_id, oficio)
WHEN 3 THEN 'TOTAL GENERAL'
WHEN 1 THEN 'Total dept. ' || dept_id
ELSE TO_CHAR(dept_id)
END AS departamento,
CASE GROUPING(oficio) WHEN 1 THEN ' ' ELSE oficio END AS puesto,
COUNT(*) AS empleados,
SUM(salario) AS masa_salarial
FROM empleados
WHERE dept_id IN (30, 50)
GROUP BY ROLLUP(dept_id, oficio)
ORDER BY dept_id, GROUPING(oficio), oficio;
DEPARTAMENTO PUESTO EMPLEADOS MASA_SALARIAL
-------------- ---------------- ---------- -------------
30 ANALISTA 1 3500
30 DIRECTOR 1 5000
30 PROGRAMADOR 3 7800
30 TÉCNICO SISTEMAS 1 2500
Total dept. 30 6 18800
50 ANALISTA 1 2700
50 DIRECTOR 1 4300
50 DISEÑADOR 1 2300
Total dept. 50 3 9300
TOTAL GENERAL 9 28100
10 filas seleccionadas.
Ejercicio 32: Pedidos por año y estado con CUBE
Muestra el número de pedidos por año y estado, con subtotales por año, por estado y total general.
Ver solución
SELECT NVL(TO_CHAR(EXTRACT(YEAR FROM fecha_pedido)), 'Todos') AS anio,
NVL(estado, 'Todos') AS estado_pedido,
COUNT(*) AS pedidos
FROM pedidos
GROUP BY CUBE(EXTRACT(YEAR FROM fecha_pedido), estado)
ORDER BY GROUPING(EXTRACT(YEAR FROM fecha_pedido)), anio,
GROUPING(estado), estado;
ANIO ESTADO_PEDID PEDIDOS
----- ------------ ----------
2025 CANCELADO 1
2025 ENTREGADO 14
2025 Todos 15
2026 ENTREGADO 4
2026 ENVIADO 2
2026 PENDIENTE 3
2026 Todos 9
Todos CANCELADO 1
Todos ENTREGADO 18
Todos ENVIADO 2
Todos PENDIENTE 3
Todos Todos 24
12 filas seleccionadas.
Ejercicio 33: Varios resúmenes a la vez
En una sola consulta, muestra el número de clientes por provincia y por comercial (pero no la combinación de ambos), solo de los clientes con límite de crédito de al menos 5000 €.
Ver solución
SELECT provincia, comercial_id, COUNT(*) AS clientes
FROM clientes
WHERE limite_credito >= 5000
GROUP BY GROUPING SETS ((provincia), (comercial_id))
ORDER BY provincia, comercial_id;
PROVINCIA COMERCIAL_ID CLIENTES
------------- ------------ ----------
Alicante (nulo) 1
Barcelona (nulo) 1
Granada (nulo) 1
Illes Balears (nulo) 1
Madrid (nulo) 1
Sevilla (nulo) 1
Valencia (nulo) 1
Zaragoza (nulo) 1
(nulo) 110 3
(nulo) 111 2
(nulo) 112 2
(nulo) 113 1
12 filas seleccionadas.
Ejercicio 34: Tabla cruzada de oficios por departamento
Muestra una tabla con una fila por departamento (20, 30, 40, 50, 60) y columnas con el número de DIRECTOR, VENDEDOR, ADMINISTRATIVO, PROGRAMADOR y OTROS.
Ver solución
SELECT dept_id,
COUNT(CASE WHEN oficio = 'DIRECTOR' THEN 1 END) AS director,
COUNT(CASE WHEN oficio = 'VENDEDOR' THEN 1 END) AS vendedor,
COUNT(CASE WHEN oficio = 'ADMINISTRATIVO' THEN 1 END) AS administrativo,
COUNT(CASE WHEN oficio = 'PROGRAMADOR' THEN 1 END) AS programador,
COUNT(CASE WHEN oficio NOT IN ('DIRECTOR', 'VENDEDOR', 'ADMINISTRATIVO', 'PROGRAMADOR')
THEN 1 END) AS otros,
COUNT(*) AS total
FROM empleados
WHERE dept_id BETWEEN 20 AND 60
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID DIRECTOR VENDEDOR ADMINISTRATIVO PROGRAMADOR OTROS TOTAL
---------- ---------- ---------- -------------- ----------- ---------- ----------
20 1 4 1 0 0 6
30 1 0 0 3 2 6
40 1 0 1 0 1 3
50 1 0 0 0 2 3
60 1 0 1 0 2 4
5 filas seleccionadas.
Ejercicio 35: Ventas por trimestre en columnas
A partir de PEDIDOS, muestra para cada año el número de pedidos no cancelados de cada trimestre en columnas T1, T2, T3 y T4.
Ver solución
SELECT EXTRACT(YEAR FROM fecha_pedido) AS anio,
SUM(CASE WHEN TO_CHAR(fecha_pedido, 'Q') = '1' THEN 1 ELSE 0 END) AS t1,
SUM(CASE WHEN TO_CHAR(fecha_pedido, 'Q') = '2' THEN 1 ELSE 0 END) AS t2,
SUM(CASE WHEN TO_CHAR(fecha_pedido, 'Q') = '3' THEN 1 ELSE 0 END) AS t3,
SUM(CASE WHEN TO_CHAR(fecha_pedido, 'Q') = '4' THEN 1 ELSE 0 END) AS t4,
COUNT(*) AS total
FROM pedidos
WHERE estado <> 'CANCELADO'
GROUP BY EXTRACT(YEAR FROM fecha_pedido)
ORDER BY anio;
ANIO T1 T2 T3 T4 TOTAL
---------- ---------- ---------- ---------- ---------- ----------
2025 6 4 1 3 14
2026 3 3 3 0 9
2 filas seleccionadas.
Con SUM(CASE ... THEN 1 ELSE 0 END) los trimestres sin pedidos muestran 0; con COUNT(CASE ... THEN 1 END) también daría 0. Con SUM(CASE ... THEN 1 END) (sin ELSE) mostraría NULL.
Ejercicio 36: Ventas con y sin descuento
A partir de LINEAS_PEDIDO, muestra para cada pedido de 2026 (pedidos 5016 a 5024) el importe bruto de las líneas sin descuento, el de las líneas con descuento y el porcentaje que supone el descuento sobre el bruto total.
Ver solución
SELECT pedido_id,
SUM(CASE WHEN descuento = 0 THEN cantidad * precio_unitario ELSE 0 END) AS sin_dto,
SUM(CASE WHEN descuento > 0 THEN cantidad * precio_unitario ELSE 0 END) AS con_dto,
ROUND(100 * SUM(cantidad * precio_unitario * descuento / 100)
/ SUM(cantidad * precio_unitario), 2) AS pct_ahorro
FROM lineas_pedido
WHERE pedido_id BETWEEN 5016 AND 5024
GROUP BY pedido_id
ORDER BY pedido_id;
PEDIDO_ID SIN_DTO CON_DTO PCT_AHORRO
---------- ---------- ---------- ----------
5016 0 9889 9,55
5017 838 0 0
5018 0 2195 5
5019 1296 0 0
5020 0 1751,2 5
5021 658 1899 3,71
5022 1499 0 0
5023 418,8 0 0
5024 1497 0 0
9 filas seleccionadas.
Ejercicios propuestos
Entre paréntesis tienes el número de filas que debe devolver cada consulta.
- Muestra la suma de presupuestos de todos los departamentos y el presupuesto medio, considerando y sin considerar el departamento sin presupuesto (1 fila).
- Muestra el número de empleados dados de alta cada año, de todos los años (15 filas).
- Muestra el número de productos descatalogados y no descatalogados (2 filas).
- Muestra los oficios cuyo salario máximo supera los 2500 € (5 filas).
- Muestra cuántos clientes se dieron de alta cada año y su límite de crédito medio (8 filas).
- Muestra los departamentos (de
EMPLEADOS) cuya masa salarial supera los 9000 € (3 filas). - Muestra las líneas de pedido agrupadas por descuento: cuántas líneas hay con cada porcentaje de descuento (6 filas).
- Muestra para cada pedido con más de 2 líneas el número de unidades totales (7 filas).
- Muestra los productos que se han vendido en más de 2 pedidos distintos (10 filas).
- Muestra el número de pedidos por estado y año usando
GROUPING SETSpara obtener solo los subtotales por estado y por año (6 filas). - Muestra para cada proyecto la lista de funciones distintas de sus empleados con
LISTAGG(DISTINCT ...)(4 filas). - Muestra cuántos empleados hay en cada tramo salarial: menos de 2000, de 2000 a 2999, de 3000 a 3999 y 4000 o más (4 filas).
- Muestra el número de clientes por provincia con una fila final de total (12 filas).
- Construye una tabla cruzada con una fila por comercial y columnas con el número de clientes de límite de crédito “bajo” (menos de 5000) y “alto” (5000 o más), incluyendo el grupo sin comercial (5 filas).
- ¿Cuál es el mayor número de pedidos que ha hecho un mismo cliente? Usa un agregado anidado (1 fila).
Resumen
| Concepto | Sintaxis | Recuerda |
|---|---|---|
| Contar filas | COUNT(*) | Incluye las filas con nulos |
| Contar valores | COUNT(col), COUNT(DISTINCT col) | Ignora nulos |
| Suma, media, extremos | SUM, AVG, MAX, MIN | Ignoran nulos; AVG(NVL(col, 0)) si el nulo es 0 |
| Estadística | STDDEV, VARIANCE, MEDIAN | Muestrales; MEDIAN es el valor central |
| Conjunto vacío | COUNT → 0; el resto → NULL | NVL(SUM(x), 0) |
| Agrupar | GROUP BY a, b | Todo lo no agregado del SELECT va en el GROUP BY |
| Agrupar por expresión | GROUP BY TO_CHAR(f, 'YYYY-MM') | Desde 23ai, también por alias |
| Por posición | GROUP BY 1 | 23ai con group_by_position_enabled = TRUE |
| Filtrar grupos | HAVING COUNT(*) > 3 | WHERE filtra filas, HAVING grupos |
| Anidar | MAX(AVG(salario)) ... GROUP BY dept_id | Solo dos niveles; resultado de una fila |
| Subtotales | ROLLUP(a, b) | (a, b), (a), () |
| Todas las combinaciones | CUBE(a, b) | (a, b), (a), (b), () |
| A medida | GROUPING SETS ((a), (b), ()) | Solo las agrupaciones indicadas |
| Etiquetar totales | GROUPING(col), GROUPING_ID(a, b) | 1 = fila de subtotal |
| Concatenar | LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col) | DISTINCT, ON OVERFLOW TRUNCATE |
| Tabla cruzada | COUNT(CASE WHEN ... THEN 1 END) | SUM(CASE ... ELSE 0 END) para importes |