Agrupaments. GROUP BY

Referència

• https://www.sqltutorial.org/sql-group-by/

Teoria

SELECT
   column1,
   column2,
   AGGREGATE_FUNCTION (column3)
FROM
   table1
GROUP BY
   column1,
   column2;

SELECT
   department_id,
   COUNT(employee_id) headcount
FROM employees
GROUP BY department_id;

SQL GROUP BY with INNER JOIN example

SELECT
	e.department_id,
	department_name,
	COUNT(employee_id) headcount
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id;

SQL GROUP BY with ORDER BY example

SELECT 
    e.department_id,
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id
ORDER BY headcount DESC;

SQL GROUP BY with HAVING example

SELECT 
    e.department_id,
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id
HAVING headcount > 5
ORDER BY headcount DESC;

SQL GROUP BY with MIN, MAX, and AVG example

SELECT 
    e.department_id,
    department_name,
    MIN(salary) min_salary,
    MAX(salary) max_salary,
    ROUND(AVG(salary), 2) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id;

SQL GROUP BY with SUM function example

SELECT 
    e.department_id,
    department_name,
    SUM(salary) total_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id;

SQL GROUP BY multiple columns

SELECT 
    e.department_id,
    department_name,
    e.job_id,
    job_title,
    COUNT(employee_id)
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
        INNER JOIN
    jobs j ON j.job_id = e.job_id
GROUP BY e.department_id , e.job_id;

SQL GROUP BY and DISTINCT

SELECT DISTINCT
	phone_number
FROM
	employees;

Having

La clàusula HAVING permet especificar una condició per als agrupaments que fem sobre la condició GROUP BY. No l'hem de confondre amb un WHERE.

Per trobar els managers que tinguin com a mínim 5 subordinats, hem d'afegir la clàusula HAVING:

SELECT 
    manager_id,
    first_name,
    last_name,
    COUNT(employee_id) direct_reports
FROM
    employees
WHERE
    manager_id IS NOT NULL
GROUP BY manager_id
HAVING direct_reports >= 5;

Per calcular la suma total dels salaris que la companyia paga a cada departament, i que aquest total estigui entre 20000 i 30000:

SELECT 
    department_id, SUM(salary)
FROM
    employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 30000
ORDER BY SUM(salary);

Per trobar els departaments que tenen empleats amb el salari més petit de qualsevol empleat més gran que 10000:

SELECT
	e.department_id,
	department_name,
	MIN(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
HAVING
	MIN(salary) >= 10000
ORDER BY
	MIN(salary);

Per trobar els departments que tenen com a salari mig dels seus empleats entre 5000 i 7000:

SELECT
	e.department_id,
	department_name,
	ROUND(AVG(salary), 2)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
HAVING
	AVG(salary) BETWEEN 5000
AND 7000
ORDER BY
	AVG(salary);

Having vs WHERE. No és el mateix

1. Departaments que el seu salari mig sigui >= 5000:

SELECT 
    e.department_id,
    department_name,
    ROUND(AVG(salary),2) avg_salary
FROM
employees e INNER JOIN
departments d ON d.department_id = e.department_id

GROUP BY e.department_id
HAVING avg_salary >= 10000;

+---------------+------------------+------------+
| department_id | department_name  | avg_salary |
+---------------+------------------+------------+
|             7 | Public Relations |   10000.00 |
|             9 | Executive        |   19333.33 |
|            11 | Accounting       |   10150.00 |
+---------------+------------------+------------+

2. Excloent els empleats que tenen un salari més petit que 10000, calcular el salari mig dels departaments que sigui >= 10000:

SELECT 
    e.department_id,
    department_name,
    ROUND(AVG(salary),2) avg_salary
FROM 
employees e INNER JOIN
departments d ON d.department_id = e.department_id

WHERE salary >= 10000

GROUP BY e.department_id
HAVING avg_salary >= 10000;

+---------------+------------------+------------+
| department_id | department_name  | avg_salary |
+---------------+------------------+------------+
|             9 | Executive        |   19333.33 |
|            10 | Finance          |   12000.00 |
|             3 | Purchasing       |   11000.00 |
|             8 | Sales            |   13750.00 |
|             2 | Marketing        |   13000.00 |
|             7 | Public Relations |   10000.00 |
|            11 | Accounting       |   12000.00 |
+---------------+------------------+------------+

Evidentment ara surten molts més departaments, perquè hem calculat el salari mig del departament només d'aquelles persones que tenen un salari >= 10000. A més, el valor que surt del salari mig és diferent, perquè la manera com es calcula és diferent. Per exemple, fixar-se en el departament 11.

---

creat per Joan Quintana Compte, gener 2022