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

---

creat per Joan Quintana Compte, gener 2022