- Preparar
- Clausula GROUP BY
- Joins
- HAVING Clause
Preparar
Puede realizar todas estas consultas en línea de forma gratuita utilizando SQL Fiddle.
Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».
Cláusula GROUP BY
SQL incluye muchas funciones agregadas, que toman información de varias filas y la agregan para producir menos filas en el conjunto de resultados final. El siguiente código contiene un ejemplo simple del uso de las funciones agregadas COUNT, AVG y SUM. Tenemos 14 filas en la tabla EMPLOYEES, pero la consulta produce una sola fila con el recuento de las filas en la tabla, así como el promedio y el total de la columna SALARIO para todas las filas de la tabla.
SELECT COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e;
EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- ---------- ----------
14 2073.21429 29025
1 row selected.
SQL>
Sin una cláusula GROUP BY, todo el conjunto de resultados se trata como un solo grupo, por lo que las funciones agregadas funcionan en el conjunto de resultados como un todo. La adición de la cláusula GROUP BY divide los resultados en grupos de filas, y las funciones agregadas se aplican de forma grupal. El siguiente ejemplo agrupa las filas por DEPARTMENT_ID, por lo que los agregados son por departamento.
SELECT e.department_id,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e
GROUP BY e.department_id
ORDER BY e.department_id;
DEPARTMENT_ID EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
------------- -------------- ---------- ----------
10 3 2916.66667 8750
20 5 2175 10875
30 6 1566.66667 9400
3 rows selected.
SQL>
Cuantas más columnas se incluyan en la cláusula GROUP BY, más granulares serán los agregados. El siguiente ejemplo agrupa por las columnas DEPARTMENT_ID y JOB.
SELECT e.department_id,
e.job,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e
GROUP BY e.department_id, e.job
ORDER BY e.department_id, e.job;
DEPARTMENT_ID JOB EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
------------- --------- -------------- ---------- ----------
10 CLERK 1 1300 1300
10 MANAGER 1 2450 2450
10 PRESIDENT 1 5000 5000
20 ANALYST 2 3000 6000
20 CLERK 2 950 1900
20 MANAGER 1 2975 2975
30 CLERK 1 950 950
30 MANAGER 1 2850 2850
30 SALESMAN 4 1400 5600
9 rows selected.
SQL>
Recuerde, todas las columnas no agregadas deben incluirse en la cláusula GROUP BY.
Joins
Una consulta agrupada puede unirse a otras tablas para proporcionar información adicional. En el siguiente ejemplo, queremos reportar algunos datos agregados basados en el departamento, pero queremos mostrar DEPARTMENT_NAME, en lugar de DEPARTMENT_ID, por lo que tenemos que unirnos a las tablas DEPARTMENT.
SELECT d.department_name,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING 3 2916.66667 8750
RESEARCH 5 2175 10875
SALES 6 1566.66667 9400
3 rows selected.
SQL>
Esto funciona bien, pero estamos perdiendo una información importante. El departamento de OPERATIONS no tiene empleados, pero no lo mostramos porque hemos utilizado INNER JOIN. Cambiar a LEFT OUTER JOIN parece solucionar ese problema, pero informa el número incorrecto de empleados.
SELECT d.department_name,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING 3 2916.66667 8750
OPERATIONS 1
RESEARCH 5 2175 10875
SALES 6 1566.66667 9400
4 rows selected.
SQL>
Ahora tenemos una fila para el departamento de OPERATIONS, la llamada COUNT(*) ha devuelto correctamente el valor 1, pero este no es el número correcto de empleados. Esto no es culpa de GROUP BY, sino de la forma en que funciona la llamada COUNT(*). Si, en cambio, hacemos referencia a una columna obligatoria de la tabla unida externamente en la llamada COUNT, obtendremos el resultado correcto, ya que los valores NULL no se cuentan. El siguiente ejemplo muestra cómo.
SELECT d.department_name,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING 3 2916.66667 8750
OPERATIONS 0
RESEARCH 5 2175 10875
SALES 6 1566.66667 9400
4 rows selected.
SQL>
HAVING Clausula
Mencionamos anteriormente, tener más columnas referenciadas en la cláusula GROUP BY nos brinda información más granular. El siguiente ejemplo agrupa los datos por DEPARTMENT_NAME y JOB.
SELECT d.department_name, e.job,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
ORDER BY d.department_name, e.job;
DEPARTMENT_NAM JOB EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- --------- -------------- ---------- ----------
ACCOUNTING CLERK 1 1300 1300
ACCOUNTING MANAGER 1 2450 2450
ACCOUNTING PRESIDENT 1 5000 5000
OPERATIONS 0
RESEARCH ANALYST 2 3000 6000
RESEARCH CLERK 2 950 1900
RESEARCH MANAGER 1 2975 2975
SALES CLERK 1 950 950
SALES MANAGER 1 2850 2850
SALES SALESMAN 4 1400 5600
10 rows selected.
SQL>
A veces, solo nos interesa información de grupos específicos de datos. La cláusula HAVING nos permite filtrar los grupos que no cumplen con un requisito específico. Puede pensar en ello como una cláusula WHERE para la cláusula GROUP BY. En el siguiente ejemplo, solo devolvemos información para grupos con más de 1 empleado.
SELECT d.department_name, e.job,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
HAVING COUNT(e.employee_id) > 1
ORDER BY d.department_name, e.job;
DEPARTMENT_NAM JOB EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- --------- -------------- ---------- ----------
RESEARCH ANALYST 2 3000 6000
RESEARCH CLERK 2 950 1900
SALES SALESMAN 4 1400 5600
3 rows selected.
SQL>
Aparte de una tontería, es posible tener una cláusula HAVING sin una cláusula GROUP BY, como se muestra en el siguiente ejemplo. Todo el conjunto de resultados se trata como un solo grupo, por lo que aún se puede aplicar el archivador de la cláusula HAVING.
SELECT COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
HAVING COUNT(e.employee_id) > 2
ORDER BY d.department_name, e.job;
EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- ---------- ----------
14 2073.21429 29025
1 row selected.
SQL>
