SQL para Principiantes (Parte 7): La Cláusula GROUP BY y la Cláusula HAVING

  • 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>

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *