Salta el contingut

Consultes avançades - GROUP BY - HAVING

GROUP BY

Fins ara hem estat aplicant les funcions de grup a totes les files de la consulta.

Podem agrupar les files que selecciona la consulta amb la clàusula WHERE i aplicar les funcions d'agrupació a cada grup.

Per això utilitzem la clàusula GROUP BY que, igual que ORDER BY, pot incloure més d'una columna.

GROUP BY permet dividir les files retornades per la consulta en diferents grups que tenen quelcom en comú.

Agrupem per DEPARTMENT_ID i comptem les files de cada agrupació.

SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
+---------------+----------+
| DEPARTMENT_ID | COUNT(*) |
+---------------+----------+
|          NULL |        1 |
|            10 |        1 |
|            20 |        2 |
|            50 |        5 |
|            60 |        3 |
|            80 |        3 |
|            90 |        3 |
|           110 |        2 |
+---------------+----------+

Agrupem per codi de departament i demanem el sou màxim de cada departament.

SELECT DEPARTMENT_ID,
       MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
+---------------+-------------+
| DEPARTMENT_ID | MAX(SALARY) |
+---------------+-------------+
|          NULL |     7000.00 |
|            10 |     4400.00 |
|            20 |    13000.00 |
|            50 |     5800.00 |
|            60 |     9000.00 |
|            80 |    11000.00 |
|            90 |    24000.00 |
|           110 |    12000.00 |
+---------------+-------------+

Normalment voldrem incloure les columnes del GROUP BY a la clàusula SELECT.

Les sentències amb GROUP BY necessiten que qualsevol columna mostrada a la clàusula SELECT que no formi part d'una funció de grup apareixi a la clàusula GROUP BY.

GROUP BY fet de forma CORRECTA

SELECT department_id, MAX(salary)
FROM   employees
GROUP BY department_id;

GROUP BY fet de forma INCORRECTA

SELECT job_id, last_name, AVG(salary)
FROM   employees
GROUP BY job_id;

L'error està en posar un camp sense funció d'agrupació al SELECT que no està al GROUP BY.

En algunes ocasions MySQL i MariaDB no dona error però és igualment incorrecte.

GROUP BY i JOIN

La clàusula GROUP BY també pot aplicar-se en comandes SELECT juntament amb JOIN, d'aquesta manera podem arribar a tenir dades d'altres taules.

SELECT DEPARTMENT_ID, DEPARTMENT_NAME,
       SUM(SALARY)
FROM   EMPLOYEES JOIN 
       DEPARTMENTS USING (DEPARTMENT_ID)
GROUP BY DEPARTMENT_ID, DEPARTMENT_NAME;
+---------------+-----------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME | SUM(SALARY) |
+---------------+-----------------+-------------+
|            10 | Administration  |     4400.00 |
|            20 | Marketing       |    19000.00 |
|            50 | Shipping        |    17500.00 |
|            60 | IT              |    19200.00 |
|            80 | Sales           |    30100.00 |
|            90 | Executive       |    58000.00 |
|           110 | Accounting      |    20300.00 |
+---------------+-----------------+-------------+

Regles pel #ªsql GROUP BY

  • Les columnes individuals que tinguem en una SELECT amb funcions de grup han d'aparèixer tots com a mínim en la clàusula GROUP BY.

  • No es poden utilitzar alies en les clàusules GROUP BY.

  • La clàusula WHERE exclou les files abans de fer-se les agrupacions.

Grups dins de Grups

  • Si volem fer agrupacions per més d'una columna també podem fer-ho afegint més columnes a la clàusula GROUP BY.

  • En aquest cas cada grup estarà format per les files que tinguin tots el camps del GROUP BY en comú.

SELECT DEPARTMENT_ID, JOB_ID,
       COUNT(*)
FROM   EMPLOYEES
GROUP BY DEPARTMENT_ID,JOB_ID;
+---------------+------------+----------+
| DEPARTMENT_ID | JOB_ID     | COUNT(*) |
+---------------+------------+----------+
|          NULL | SA_REP     |        1 |
|            10 | AD_ASST    |        1 |
|            20 | MK_MAN     |        1 |
|            20 | MK_REP     |        1 |
|            50 | ST_CLERK   |        4 |
|            50 | ST_MAN     |        1 |
|            60 | IT_PROG    |        3 |
|            80 | SA_MAN     |        1 |
|            80 | SA_REP     |        2 |
|            90 | AD_PRES    |        1 |
|            90 | AD_VP      |        2 |
|           110 | AC_ACCOUNT |        1 |
|           110 | AC_MGR     |        1 |
+---------------+------------+----------+

Anidament de funcions de grup

MariaDB i MySQL no permeten resoldre funcions d'agrupament del següent tipus, que sí es pot fer en ORACLE.

SELECT MAX(AVG(salary))
FROM employees
GROUP by department_id;
Per a resoldre la consulta farem ús d'una subconsulta, que s'explicarà més endavant.

SELECT AVG(SALARY)
FROM employees
GROUP by department_id
HAVING AVG(SALARY) >= ALL (SELECT AVG(SALARY)
                           FROM employees
                           GROUP by department_id);

HAVING

  • Una vegada hem agrupat amb la clàusula GROUP BY podem seleccionar alguns grups amb la clàusula HAVING.

  • La clausula HAVING equival al WHERE una vegada fetes les agrupacions.

  • A la clàusula HAVING només hi posarem condicions amb funcions de grup (normalment).

La condició que posem a la clàusula HAVING s'avalua després d'haver fet les agrupacions.

SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 2;
+---------------+----------+
| DEPARTMENT_ID | COUNT(*) |
+---------------+----------+
|            50 |        5 |
|            60 |        3 |
|            80 |        3 |
|            90 |        3 |
+---------------+----------+

ja que ens hem quedat només les agrupacions on COUNT(*) > 2

+---------------+----------+
| DEPARTMENT_ID | COUNT(*) |
+---------------+----------+
|          NULL |        1 |
|            10 |        1 |
|            20 |        2 |
|            50 |        5 |
|            60 |        3 |
|            80 |        3 |
|            90 |        3 |
|           110 |        2 |
+---------------+----------+

Un altre exemple

Mitjana de sou i nombre d'empleats dels tipus de feina amb tres o més treballadors
SELECT JOB_ID, AVG(SALARY), COUNT(*)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(*) >=3;
+----------+-------------+----------+
| JOB_ID   | AVG(SALARY) | COUNT(*) |
+----------+-------------+----------+
| IT_PROG  | 6400.000000 |        3 |
| SA_REP   | 8866.666667 |        3 |
| ST_CLERK | 2925.000000 |        4 |
+----------+-------------+----------+