Funcions i agrupació

Funcions d'agregació

Les funcions d'agregació calculen un valor únic a partir d'un conjunt de files. Son la base de qualsevol anàlisi estadística en SQL.

Funció	Descripció	Notes
COUNT(*)	Nombre total de files	Compta totes les files, incloses les amb NULL
COUNT(columna)	Nombre de valors no NULL	Ignora els NULL
COUNT(DISTINCT col)	Nombre de valors únics	Ignora NULL i duplicats
SUM(columna)	Suma dels valors	Retorna NULL si tots son NULL
AVG(columna)	Mitjana aritmètica	Ignora els NULL
MAX(columna)	Valor màxim	Funciona amb números, text i dates
MIN(columna)	Valor mínim	Idem

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Estadístiques generals de la taula de productes
SELECT
    COUNT(*) AS total_productes,
    COUNT(descripcio) AS amb_descripcio,
    COUNT(DISTINCT categoria) AS num_categories,
    SUM(estoc) AS estoc_total,
    AVG(preu) AS preu_mitja,
    MIN(preu) AS preu_minim,
    MAX(preu) AS preu_maxim,
    SUM(preu * estoc) AS valor_total_estoc
FROM productes
WHERE actiu = TRUE;

SELECT
    COUNT(*) AS total_productes,
    COUNT(descripcio) AS amb_descripcio,
    COUNT(DISTINCT categoria) AS num_categories,
    SUM(estoc) AS estoc_total,
    AVG(preu) AS preu_mitja,
    MIN(preu) AS preu_minim,
    MAX(preu) AS preu_maxim,
    SUM(preu * estoc) AS valor_total_estoc
FROM productes
WHERE actiu = 1;

SELECT
    COUNT(*) AS total_productes,
    COUNT(descripcio) AS amb_descripcio,
    COUNT(DISTINCT categoria) AS num_categories,
    SUM(estoc) AS estoc_total,
    AVG(preu) AS preu_mitja,
    MIN(preu) AS preu_minim,
    MAX(preu) AS preu_maxim,
    SUM(preu * estoc) AS valor_total_estoc
FROM productes
WHERE actiu = 1;

SELECT
    COUNT(*) AS total_productes,
    COUNT(descripcio) AS amb_descripcio,
    COUNT(DISTINCT categoria) AS num_categories,
    SUM(estoc) AS estoc_total,
    AVG(preu) AS preu_mitja,
    MIN(preu) AS preu_minim,
    MAX(preu) AS preu_maxim,
    SUM(preu * estoc) AS valor_total_estoc
FROM productes
WHERE actiu = 1;

AVG i els NULL

AVG(columna) ignora automàticament els valors NULL. Si voleu una mitjana que tracti NULL com a zero, cal usar AVG(COALESCE(columna, 0)). La funció COALESCE(val1, val2) retorna el primer valor no NULL.

---

GROUP BY

GROUP BY agrupa les files que tenen els mateixos valors en les columnes especificades i aplica les funcions d'agregació a cada grup.

Regla fonamental: En una consulta amb GROUP BY, el SELECT només pot contenir:

1. Les columnes que apareixen al GROUP BY.
2. Funcions d'agregació.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Nombre de productes per categoria
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
ORDER BY num_productes DESC;

-- Vendes totals per client
SELECT
    c.id_client,
    c.nom || ' ' || c.cognoms AS client,
    COUNT(co.id_comanda) AS num_comandes,
    SUM(co.total) AS total_venut,
    AVG(co.total) AS ticket_mig
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE co.estat = 'entregada'
GROUP BY c.id_client, c.nom, c.cognoms
ORDER BY total_venut DESC;

-- Agrupació per data (anys)
SELECT
    DATE_TRUNC('month', data_com) AS mes,
    COUNT(*) AS num_comandes,
    SUM(total) AS total_mes
FROM comandes
GROUP BY DATE_TRUNC('month', data_com)
ORDER BY mes;

-- Nombre de productes per categoria
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
ORDER BY num_productes DESC;

-- Vendes totals per client
SELECT
    c.id_client,
    CONCAT(c.nom, ' ', c.cognoms) AS client,
    COUNT(co.id_comanda) AS num_comandes,
    SUM(co.total) AS total_venut,
    AVG(co.total) AS ticket_mig
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE co.estat = 'entregada'
GROUP BY c.id_client, c.nom, c.cognoms
ORDER BY total_venut DESC;

-- Agrupació mensual
SELECT
    DATE_FORMAT(data_com, '%Y-%m') AS mes,
    COUNT(*) AS num_comandes,
    SUM(total) AS total_mes
FROM comandes
GROUP BY DATE_FORMAT(data_com, '%Y-%m')
ORDER BY mes;

-- Nombre de productes per categoria
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
ORDER BY num_productes DESC;

-- Vendes totals per client
SELECT
    c.id_client,
    c.nom + ' ' + c.cognoms AS client,
    COUNT(co.id_comanda) AS num_comandes,
    SUM(co.total) AS total_venut,
    AVG(co.total) AS ticket_mig
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE co.estat = 'entregada'
GROUP BY c.id_client, c.nom, c.cognoms
ORDER BY total_venut DESC;

-- Nombre de productes per categoria
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
ORDER BY num_productes DESC;

-- Vendes totals per client
SELECT
    c.id_client,
    c.nom || ' ' || c.cognoms AS client,
    COUNT(co.id_comanda) AS num_comandes,
    SUM(co.total) AS total_venut,
    AVG(co.total) AS ticket_mig
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE co.estat = 'entregada'
GROUP BY c.id_client, c.nom, c.cognoms
ORDER BY total_venut DESC;

-- Agrupació mensual (TRUNC trunca la data al primer dia del mes)
SELECT
    TRUNC(data_com, 'MM') AS mes,
    COUNT(*) AS num_comandes,
    SUM(total) AS total_mes
FROM comandes
GROUP BY TRUNC(data_com, 'MM')
ORDER BY mes;

---

HAVING

HAVING filtra els grups resultants del GROUP BY, de la mateixa manera que WHERE filtra les files individuals. La diferència clau:

• WHERE s'avalua antes del GROUP BY (filtra files individuals).
• HAVING s'evalua després del GROUP BY (filtra grups).

flowchart LR
    FROM["FROM\n(totes les files)"] --> WHERE["WHERE\n(filtra files)"]
    WHERE --> GROUP["GROUP BY\n(agrupa)"]
    GROUP --> HAVING["HAVING\n(filtra grups)"]
    HAVING --> SELECT["SELECT\n(projecta)"]
    SELECT --> ORDER["ORDER BY\n(ordena)"]
    ORDER --> LIMIT["LIMIT\n(limita)"]

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Categories amb més de 5 productes
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
HAVING COUNT(*) > 5
ORDER BY num_productes DESC;

-- Clients que han gastat més de 1000 euros en total
SELECT
    c.nom || ' ' || c.cognoms AS client,
    SUM(co.total) AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom, c.cognoms
HAVING SUM(co.total) > 1000
ORDER BY total_gastat DESC;

-- Mesos amb més de 100 comandes
SELECT
    DATE_TRUNC('month', data_com) AS mes,
    COUNT(*) AS num_comandes
FROM comandes
GROUP BY DATE_TRUNC('month', data_com)
HAVING COUNT(*) > 100
ORDER BY mes;

-- Categories amb més de 5 productes
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
HAVING COUNT(*) > 5
ORDER BY num_productes DESC;

-- Clients que han gastat més de 1000 euros en total
SELECT
    CONCAT(c.nom, ' ', c.cognoms) AS client,
    SUM(co.total) AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom, c.cognoms
HAVING SUM(co.total) > 1000
ORDER BY total_gastat DESC;

-- Mesos amb més de 100 comandes
SELECT
    DATE_FORMAT(data_com, '%Y-%m') AS mes,
    COUNT(*) AS num_comandes
FROM comandes
GROUP BY DATE_FORMAT(data_com, '%Y-%m')
HAVING COUNT(*) > 100
ORDER BY mes;

-- Categories amb més de 5 productes
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
HAVING COUNT(*) > 5
ORDER BY num_productes DESC;

-- Clients que han gastat més de 1000 euros en total
SELECT
    c.nom + ' ' + c.cognoms AS client,
    SUM(co.total) AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom, c.cognoms
HAVING SUM(co.total) > 1000
ORDER BY total_gastat DESC;

-- Categories amb més de 5 productes
SELECT categoria, COUNT(*) AS num_productes
FROM productes
GROUP BY categoria
HAVING COUNT(*) > 5
ORDER BY num_productes DESC;

-- Clients que han gastat més de 1000 euros en total
SELECT
    c.nom || ' ' || c.cognoms AS client,
    SUM(co.total) AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom, c.cognoms
HAVING SUM(co.total) > 1000
ORDER BY total_gastat DESC;

-- Mesos amb més de 100 comandes
SELECT
    TRUNC(data_com, 'MM') AS mes,
    COUNT(*) AS num_comandes
FROM comandes
GROUP BY TRUNC(data_com, 'MM')
HAVING COUNT(*) > 100
ORDER BY mes;

WHERE vs HAVING: l'error més comú

L'error més freqüent és usar HAVING quan caldria WHERE (o viceversa). Recordeu: si el criteri de filtratge no implica una funció d'agregació, useu WHERE. Si implica una funció d'agregació (COUNT, SUM, AVG...), useu HAVING.

-- INCORRECTE: WHERE no pot contenir COUNT()
SELECT categoria, COUNT(*) FROM productes
WHERE COUNT(*) > 5
GROUP BY categoria;

-- CORRECTE:
SELECT categoria, COUNT(*) FROM productes
GROUP BY categoria
HAVING COUNT(*) > 5;

---

AC0372/04/03 — Miniactivitat

RA4 · CA4.5, CA4.6

Usant la vostra BD de pràctiques, escriviu consultes per a:

1. Nombre de reserves per instal·lació, ordenat per nombre de reserves descendent.
2. Usuaris que han fet més de 3 reserves.
3. La instal·lació mes reservada cada cop més (agrupant per mes i instal·lació).
4. El preu mig de les reserves per estat (pendent, confirmada, cancel·lada).
5. Categories d'instal·lació amb una recaptació total superior a 500 euros.