El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Funcions d'agregació: COUNT, SUM, AVG, MIN i MAX

Següent ►
►

Fins ara, cada fila dels teus resultats venia d'una fila de la base de dades. SELECT projectava columnes, WHERE descartava files, JOIN combinava taules: la granularitat es podia multiplicar, però continuaves treballant fila a fila. Les funcions d'agregació trenquen aquella correspondència. Reben moltes files i retornen un sol valor: un total, un recompte, una mitjana, un màxim.

És el pas que converteix una consulta en un informe. "Quant hem facturat?", "quantes comandes hi ha pendents?", "quin és el tiquet mitjà?" són preguntes que cap consulta anterior no podia respondre. En aquesta lliçó aprendràs les cinc funcions fonamentals, veuràs per què totes ignoren els NULL llevat de COUNT(*) —i per què això de vegades et convé i de vegades t'enganya—, descobriràs que SUM d'un conjunt buit retorna NULL mentre que COUNT retorna 0, i resoldràs per fi l'avís que el mòdul 3 et va repetir tres vegades: sumar les despeses d'enviament després d'unir amb el detall infla el total. Veuràs el número malament, el número bé i les dues formes correctes de plantejar-ho.

Contingut

  1. Què és una funció d'agregació
  2. Les cinc funcions i els seus tipus de retorn
  3. Les tres formes de COUNT
  4. SUM i AVG sobre el detall de vendes
  5. AVG ignora els NULL: dues respostes correctes per a preguntes diferents
  6. MIN i MAX sobre números, dates i text
  7. El conjunt buit: SUM dona NULL, COUNT dona 0
  8. Un agregat col·lapsa tota la taula, i no es pot barrejar amb una columna solta
  9. FILTER (WHERE ...): agregar només una part
  10. STRING_AGG i ARRAY_AGG: agregats no numèrics
  11. La resolució de l'avís del mòdul 3
  12. Errors habituals i consells
  13. Exercicis
  14. Conclusió

  1. Què és una funció d'agregació

Una funció d'agregació recorre un conjunt de files i produeix un únic valor.

flowchart LR
    subgraph E["Entrada: 47 files de linies_comanda"]
      A["23.90"]
      B["11.70"]
      C["6.50"]
      D["…"]
    end
    E --> F["SUM( )"]
    F --> G["727.95<br/>1 fila, 1 valor"]

La consulta més simple possible amb un agregat:

SELECT COUNT(*) AS total_linies
FROM linies_comanda;
total_linies
47

Una sola fila, encara que la taula en tingui 47. Aquell col·lapse és la característica que defineix l'operació, i d'ell en surten totes les regles de la lliçó.

Dues observacions que convé fixar des del principi:

  • Un agregat sense GROUP BY col·lapsa la taula sencera en una fila. Sempre en retorna exactament una, fins i tot si la taula és buida.
  • Les funcions d'agregació no es poden fer servir al WHERE. El WHERE decideix quines files entren al conjunt, així que no pot dependre d'un càlcul fet sobre aquell mateix conjunt. Per filtrar per un agregat existeix HAVING, i és la lliçó 04-06.

  1. Les cinc funcions i els seus tipus de retorn

Funció Què retorna Tipus que accepta Ignora els NULL? Conjunt buit
COUNT(*) Nombre de files — No aplica 0
COUNT(expr) Nombre de valors no nuls Qualsevol Sí 0
SUM(expr) Suma Numèrics, INTERVAL Sí NULL
AVG(expr) Mitjana aritmètica Numèrics, INTERVAL Sí NULL
MIN(expr) Valor mínim Qualsevol tipus ordenable Sí NULL
MAX(expr) Valor màxim Qualsevol tipus ordenable Sí NULL

I els tipus de retorn a PostgreSQL, que importen més del que sembla:

Expressió d'entrada COUNT SUM AVG MIN / MAX
SMALLINT / INTEGER BIGINT BIGINT NUMERIC mateix tipus
BIGINT BIGINT NUMERIC NUMERIC BIGINT
NUMERIC BIGINT NUMERIC NUMERIC NUMERIC
REAL / DOUBLE BIGINT DOUBLE DOUBLE mateix tipus
DATE, TEXT, BOOLEAN BIGINT — — mateix tipus

Dos detalls amb conseqüències pràctiques:

  1. SUM d'enters retorna BIGINT, no INTEGER. És una protecció contra el desbordament: sumar un milió d'enters grans desborda INTEGER amb facilitat.
  2. AVG d'un enter retorna NUMERIC, no un enter. PostgreSQL no trunca. Ho veuràs a la secció 6, i és una diferència important amb altres motors.

  1. Les tres formes de COUNT

COUNT té tres escriptures que gairebé mai no donen el mateix número, i confondre-les és una de les fonts d'error més habituals en informes.

Escriptura Compta
COUNT(*) Files. Totes, tinguin els valors que tinguin
COUNT(columna) Valors no nuls d'aquella columna
COUNT(DISTINCT columna) Valors diferents i no nuls d'aquella columna

La millor demostració possible és a comandes.empleat_id, que té 20 files, 10 valors i 3 comercials diferents:

SELECT COUNT(*)                    AS files,
       COUNT(empleat_id)           AS amb_comercial,
       COUNT(DISTINCT empleat_id)  AS comercials_diferents
FROM comandes;
files amb_comercial comercials_diferents
20 10 3

20, 10 i 3. Tres números sobre la mateixa columna de la mateixa taula, i els tres són correctes perquè responen preguntes diferents:

  • 20: quantes comandes hi ha? Totes les comandes existeixen, tinguin comercial o no.
  • 10: quantes comandes va gestionar un comercial? Les deu del canal telefònic. Els NULL del canal web no es compten.
  • 3: quants comercials han gestionat alguna comanda? L'Óscar (4), la Laia (5) i en Marc (6). Els altres cinc empleats no hi apareixen mai.

Aquesta és la prova definitiva que els agregats ignoren els NULL: COUNT(*) és l'única forma que compta les deu files del canal web, perquè és l'única que no mira cap valor.

flowchart TD
    A["20 files de comandes"] --> B["COUNT(*)<br/>compta files<br/>→ 20"]
    A --> C["COUNT(empleat_id)<br/>descarta els 10 NULL<br/>→ 10"]
    A --> D["COUNT(DISTINCT empleat_id)<br/>descarta NULL i duplicats<br/>→ 3"]

Quan fer servir cadascuna

Pregunta de negoci Escriptura correcta
"Quantes comandes hem rebut?" COUNT(*)
"Quantes comandes porten comercial assignat?" COUNT(empleat_id)
"Quants comercials estan actius en vendes?" COUNT(DISTINCT empleat_id)
"Quants clients diferents han comprat?" COUNT(DISTINCT client_id)

Aquest últim mereix veure's, perquè connecta amb el DISTINCT de 02-04 i amb els clients desapareguts de 03-02:

SELECT COUNT(*)                  AS comandes,
       COUNT(DISTINCT client_id) AS clients_compradors
FROM comandes;
comandes clients_compradors
20 12

12 dels 15 clients han comprat alguna vegada. Els tres que falten són la Núria, l'Hugo i la Inés, exactament els que vas recuperar amb l'anti-join de 03-03. I fixa't en una cosa important: COUNT(DISTINCT client_id) sobre comandes no et pot dir que en total en són 15, perquè a comandes no hi existeixen. Per a això cal partir de clients.

Avís de rendiment: COUNT(DISTINCT columna) és notablement més car que COUNT(columna), perquè obliga el motor a ordenar o a construir una taula hash amb tots els valors. Amb 20 files és irrellevant; amb cent milions, és la diferència entre un segon i uns quants minuts. Fes-lo servir quan el necessitis, no per costum.

Nota de dialecte: COUNT(DISTINCT a, b) amb diverses columnes funciona a MySQL però no a PostgreSQL, on cal escriure COUNT(DISTINCT (a, b)) fent servir la sintaxi de fila composta. I COUNT(*) enfront de COUNT(1): a PostgreSQL són idèntics en rendiment i tots dos s'optimitzen igual, així que l'elecció és purament d'estil. El curs fa servir COUNT(*).

  1. SUM i AVG sobre el detall de vendes

Ara la pregunta que el negoci fa de debò: quant hem facturat? L'import d'una línia és el de sempre, i es calcula amb lc.preu_unitari (la regla de 03-02), mai amb p.preu.

SELECT COUNT(*)                                                  AS linies,
       SUM(lc.quantitat)                                         AS unitats,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio,
       ROUND(AVG(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS import_mitja_linia
FROM linies_comanda AS lc;
linies unitats facturacio import_mitja_linia
47 113 727.95 15.49

Aquestes són les xifres mestres de BotigaVerda: 47 línies, 113 unitats venudes i 727,95 € de facturació de producte (sense comptar les despeses d'enviament, que veurem a la secció 11).

Per què s'agrega sobre preu_unitari i no sobre productes.preu

És el moment de comprovar amb números el que 03-02 anunciava. Les línies 1 i 4 porten preu històric, anterior a la pujada de tarifes de l'abril del 2025:

SELECT lc.id,
       p.nom AS producte,
       lc.quantitat,
       lc.preu_unitari                          AS preu_cobrat,
       p.preu                                   AS preu_actual,
       ROUND(lc.quantitat * lc.preu_unitari, 2) AS import_real,
       ROUND(lc.quantitat * p.preu, 2)          AS import_amb_el_preu_actual
FROM linies_comanda AS lc
JOIN productes AS p ON lc.producte_id = p.id
WHERE lc.preu_unitari <> p.preu
ORDER BY lc.id;
id producte quantitat preu_cobrat preu_actual import_real import_amb_el_preu_actual
1 Oli d'oliva verge extra 500 ml 2 11.95 12.50 23.90 25.00
4 Crema facial d'àloe vera 50 ml 1 17.50 18.90 17.50 18.90

2 línies de 47. La diferència acumulada és de 2,50 €, xavalla en aquest conjunt de dades. Però fixa't en el que significa: si agreguessis sobre p.preu, estaries afirmant que al març del 2025 es van facturar 2,50 € que no van entrar mai a caixa. Amb un catàleg real i diverses pujades de tarifa a l'any, aquella xifra es compta en milers.

Regla del curs, ara amb agregats: SUM de vendes sempre sobre lc.preu_unitari. productes.preu serveix per respondre "quant costa avui?", mai "quant vam facturar aleshores?".

Sumar i comptar sobre un subconjunt

Els agregats es combinen amb WHERE amb tota naturalitat, i el WHERE actua abans:

SELECT COUNT(*)                                                  AS linies_2026,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio_2026
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id
WHERE co.data_comanda >= DATE '2026-01-01'
  AND co.data_comanda <  DATE '2027-01-01';
linies_2026 facturacio_2026
7 124.43

124,43 € a les quatre comandes del 2026, davant dels 603,52 € de les setze del 2025. Sumen els 727,95 € del total, com ha de ser.

  1. AVG ignora els NULL: dues respostes correctes per a preguntes diferents

Que AVG ignori els nuls sembla un detall tècnic. És una decisió de negoci disfressada, i convé veure-la amb un cas on la diferència fa mal.

La pregunta: "quin és el salari mitjà del comercial que gestiona les nostres comandes?"

SELECT COUNT(*)        AS comandes,
       COUNT(e.salari) AS comandes_amb_comercial,
       SUM(e.salari)   AS suma_salaris,
       AVG(e.salari)   AS mitjana_avg
FROM comandes AS co
LEFT JOIN empleats AS e ON co.empleat_id = e.id;
comandes comandes_amb_comercial suma_salaris mitjana_avg
20 10 274200.00 27420.000000000000

AVG ha retornat 27 420 €: ha sumat 274 200 € i ho ha dividit entre 10, no entre 20, perquè les deu comandes web tenen e.salari a NULL i AVG les ignora.

Ara l'altra lectura de la mateixa pregunta:

SELECT ROUND(SUM(e.salari) / COUNT(*), 2) AS mitjana_sobre_totes_les_files
FROM comandes AS co
LEFT JOIN empleats AS e ON co.empleat_id = e.id;
mitjana_sobre_totes_les_files
13710.00

13 710 €, exactament la meitat. I les dues xifres són correctes:

Xifra Divideix entre Respon a
27 420 € 10 (els valors no nuls) "De les comandes que porten comercial, quin és el seu salari mitjà?"
13 710 € 20 (totes les files) "Per cada comanda que entra, quant salari de comercial hi ha al darrere de mitjana?"

La segona té sentit si estàs repartint el cost comercial entre tot el volum de comandes, incloses les que no consumeixen temps de ningú. La primera, si estàs comparant perfils de comercial. Triar malament no dona error: dona una xifra que dobla o divideix per dos la realitat.

flowchart TD
    A["AVG(columna)"] --> B["SUM dels valors<br/>NO nuls"]
    A --> C["dividit entre<br/>COUNT(columna)"]
    D["Vols els NULL<br/>com a zero?"] -->|"Sí"| E["SUM(col) / COUNT(*)<br/>o AVG(COALESCE(col, 0))"]
    D -->|"No"| F["AVG(col) tal qual"]

La forma explícita de tractar els nuls com a zeros és AVG(COALESCE(e.salari, 0)), que retornaria els mateixos 13 710 €. COALESCE s'estudia a 06-04; esmenta-ho mentalment cada vegada que escriguis un AVG sobre una columna que admet nuls.

La pregunta que cal fer-se sempre abans d'escriure AVG: "la mitjana és sobre les files que tenen dada, o sobre totes les files?". Si no la pots respondre, la consulta encara no està definida.

  1. MIN i MAX sobre números, dates i text

MIN i MAX funcionen sobre qualsevol tipus que es pugui ordenar, no només sobre números. És la seva característica més infrautilitzada.

SELECT MIN(preu)       AS preu_minim,
       MAX(preu)       AS preu_maxim,
       MIN(data_alta)  AS primera_alta,
       MAX(data_alta)  AS ultima_alta,
       MIN(nom)        AS primer_alfabeticament,
       MAX(nom)        AS ultim_alfabeticament
FROM productes;
preu_minim preu_maxim primera_alta ultima_alta primer_alfabeticament ultim_alfabeticament
1.95 22.00 2025-01-15 2025-06-01 Arròs integral ecològic 1 kg Xampú sòlid de romaní 80 g

Quatre tipus de dada en una consulta: NUMERIC, DATE i TEXT. Sobre text, MIN/MAX fan servir la colació de la base (02-05), així que el resultat pot variar entre servidors configurats de manera diferent.

L'ús més freqüent a la pràctica és sobre dates, per acotar l'històric:

SELECT COUNT(*)                 AS comandes,
       MIN(data_comanda)        AS primera_comanda,
       MAX(data_comanda)        AS ultima_comanda,
       MIN(despeses_enviament)  AS enviament_minim,
       MAX(despeses_enviament)  AS enviament_maxim
FROM comandes;
comandes primera_comanda ultima_comanda enviament_minim enviament_maxim
20 2025-03-04 2026-02-21 0.00 12.50

Onze mesos i mig d'històric, del 4 de març del 2025 al 21 de febrer del 2026, amb ports entre 0,00 € (enviament gratuït) i 12,50 €.

El tipus de retorn d'AVG i per què surten tants decimals

SELECT COUNT(*)       AS ressenyes,
       MIN(puntuacio) AS pitjor,
       MAX(puntuacio) AS millor,
       AVG(puntuacio) AS mitjana_bruta
FROM ressenyes;
ressenyes pitjor millor mitjana_bruta
12 2 5 4.0833333333333333

La pitjor puntuació de tota BotigaVerda és un 2, el de la kombutxa de gingebre ("massa gingebre per al meu gust"). I fixa't en la mitjana: setze decimals. Passa perquè AVG sobre una columna SMALLINT retorna NUMERIC, i la divisió de dos NUMERIC a PostgreSQL es calcula amb almenys 16 dígits significatius per no perdre precisió. No és una errada: és la garantia que el motor no ha arrodonit pel seu compte.

La manera de presentar-ho és la de sempre: calcular amb precisió completa i arrodonir només en mostrar.

SELECT COUNT(*)                  AS ressenyes,
       ROUND(AVG(puntuacio), 2)  AS puntuacio_mitjana
FROM ressenyes;
ressenyes puntuacio_mitjana
12 4.08

Nota de dialecte: aquest és un dels punts on més divergeixen els motors, i on més errors silenciosos es produeixen en portar codi.

Motor AVG sobre una columna entera Resultat amb 49/12
PostgreSQL NUMERIC amb precisió completa 4.0833333333333333
MySQL DECIMAL 4.0833
SQLite Sempre REAL (coma flotant) 4.083333333333333
SQL Server INT: trunca 4
Oracle NUMBER 4.08333333333333…

SQL Server és el cas perillós: AVG d'una columna INT fa divisió entera i retorna 4. Per obtenir el decimal cal convertir explícitament: AVG(CAST(puntuacio AS DECIMAL(10,2))). Un informe portat de PostgreSQL a SQL Server pot començar a arrodonir a la baixa sense que ningú no se n'adoni.

  1. El conjunt buit: SUM dona NULL, COUNT dona 0

Aquest és un parany clàssic d'informes, i BotigaVerda té el cas perfecte: la categoria 6 (Complements) té un sol producte, el 20 (Càpsules d'espirulina), que està descatalogat i no s'ha venut mai.

SELECT COUNT(*)                                                  AS linies,
       SUM(lc.quantitat)                                         AS unitats,
       SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))   AS facturacio,
       AVG(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))   AS import_mitja,
       MAX(lc.quantitat)                                         AS quantitat_maxima
FROM linies_comanda AS lc
JOIN productes AS p ON lc.producte_id = p.id
WHERE p.categoria_id = 6;
linies unitats facturacio import_mitja quantitat_maxima
0 (null) (null) (null) (null)

Una sola fila, amb un 0 i quatre nuls. Dues coses per aprendre'n:

  1. Una consulta amb agregats i sense GROUP BY sempre retorna una fila, encara que el WHERE no en deixi passar cap. No retorna "0 files": retorna una fila amb el resultat d'agregar el no-res.
  2. COUNT del no-res és 0; SUM, AVG, MIN i MAX del no-res són NULL. És coherent: comptar zero elements dona zero, però la suma d'un conjunt buit no té cap valor natural per retornar, i la seva mitjana encara menys.

Per què importa: si el teu informe calcula facturacio * 1.21 per afegir-hi l'IVA, aquella cel·la mostrarà NULL, no 0.00. I si l'aplicació que consumeix el resultat espera un número, pot fallar. La solució és COALESCE(SUM(...), 0), que veuràs a 06-04.

Agregat Conjunt buit Tots els valors NULL
COUNT(*) 0 n (compta files)
COUNT(expr) 0 0
SUM(expr) NULL NULL
AVG(expr) NULL NULL
MIN / MAX NULL NULL

Fixa't en la columna de la dreta: és el mateix comportament. Per a SUM i AVG tant se val que no hi hagi files com que n'hi hagi totes amb el valor a nul. És coherent amb la regla de la secció 3: els agregats descarten els nuls abans d'operar, així que "tot nul" i "buit" acaben sent el mateix conjunt.

  1. Un agregat col·lapsa tota la taula, i no es pot barrejar amb una columna solta

Ja ho has vist: sense GROUP BY, l'agregat s'aplica a totes les files que surten del WHERE i produeix una fila. I d'aquí en surt la restricció més important d'aquesta lliçó.

-- ⚠️ INCORRECTA
SELECT p.nom,
       COUNT(*) AS productes
FROM productes AS p;
ERROR:  column "p.nom" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT p.nom,
               ^

L'error té tota la raó. Pensa-hi: COUNT(*) retornarà una sola fila amb el valor 20. Què hauria d'aparèixer a la columna nom d'aquella fila? «Oli d'oliva»? «Suc de taronja»? Els vint concatenats? La pregunta no té resposta, i PostgreSQL es nega a inventar-se'n una.

La regla general, que governarà tota la lliçó 04-05:

Tota columna del SELECT ha d'estar dins d'una funció d'agregació o formar part del GROUP BY. No hi ha una tercera opció.

Aquí hi ha tres sortides, i cadascuna respon una pregunta diferent:

Què vols Com s'escriu
Un sol número per a tota la taula SELECT COUNT(*) FROM productes;
Un número per cada categoria Afegir-hi GROUP BY categoria_id → lliçó 04-05
Un nom concret al costat del total Agregar també el nom: MIN(p.nom), o fer servir funcions de finestra (mòdul 10)

Aquesta és exactament la porta d'entrada a GROUP BY. La majoria de les preguntes de negoci no són "quant hem facturat?" sinó "quant hem facturat per categoria?", i això requereix partir la taula en grups abans d'agregar.

Nota de dialecte: MySQL amb el mode ONLY_FULL_GROUP_BY desactivat accepta aquella consulta sense protestar i retorna un valor arbitrari de nom. SQLite fa el mateix sempre. És una comoditat que produeix informes silenciosament incorrectes, i la lliçó 04-05 la tracta en detall amb una taula comparativa. Des de MySQL 5.7.5 el mode està actiu per defecte, precisament per això.

  1. FILTER (WHERE ...): agregar només una part

Sovint vols diversos agregats sobre subconjunts diferents en una mateixa fila de resultat: total, lliurades, cancel·lades. Escriure tres consultes i unir-les és tediós. PostgreSQL ofereix la clàusula FILTER:

SELECT COUNT(*)                                              AS comandes,
       COUNT(*) FILTER (WHERE estat = 'lliurat')             AS lliurats,
       COUNT(*) FILTER (WHERE estat = 'cancellat')           AS cancellats,
       COUNT(*) FILTER (WHERE empleat_id IS NULL)            AS canal_web,
       SUM(despeses_enviament)                               AS enviament_total,
       SUM(despeses_enviament) FILTER (WHERE estat = 'lliurat') AS enviament_lliurats
FROM comandes;
comandes lliurats cancellats canal_web enviament_total enviament_lliurats
20 14 1 10 118.25 76.05

Sis mètriques en una sola passada sobre la taula. La sintaxi és AGREGAT(expr) FILTER (WHERE condició): la condició decideix quines files entren en aquell agregat concret, sense afectar els altres ni el WHERE general de la consulta.

Un exemple amb diners, comparant els dos exercicis de BotigaVerda:

SELECT ROUND(SUM(imp.import), 2)                                 AS total,
       ROUND(SUM(imp.import) FILTER (WHERE imp.any_ = 2025), 2)  AS vendes_2025,
       ROUND(SUM(imp.import) FILTER (WHERE imp.any_ = 2026), 2)  AS vendes_2026,
       COUNT(*) FILTER (WHERE imp.descompte > 0)                 AS linies_amb_descompte
FROM (
    SELECT lc.quantitat * lc.preu_unitari * (1 - lc.descompte) AS import,
           lc.descompte,
           EXTRACT(YEAR FROM co.data_comanda)                  AS any_
    FROM linies_comanda AS lc
    JOIN comandes AS co ON lc.comanda_id = co.id
) AS imp;
total vendes_2025 vendes_2026 linies_amb_descompte
727.95 603.52 124.43 6

Només 6 de les 47 línies porten descompte. (EXTRACT es veu a fons a 06-03; aquí només n'extreu l'any. La subconsulta al FROM és del mòdul 7: aquí serveix únicament per no repetir tres vegades l'expressió de l'import.)

Portabilitat: FILTER és SQL estàndard però només l'implementen PostgreSQL (9.4+) i SQLite (3.30+). A MySQL, SQL Server i Oracle cal escriure la forma clàssica i portable, que consisteix a ficar un CASE dins de l'agregat:

COUNT(CASE WHEN estat = 'lliurat' THEN 1 END)  AS lliurats
SUM(CASE WHEN estat = 'lliurat' THEN despeses_enviament ELSE 0 END) AS enviament_lliurats

Funciona perquè COUNT ignora els NULL que retorna el CASE sense ELSE. És exactament el mateix mecanisme de la secció 3, aprofitat a propòsit. CASE és la lliçó 06-05.

  1. STRING_AGG i ARRAY_AGG: agregats no numèrics

No tots els agregats produeixen números. Dos de PostgreSQL són especialment útils i convé conèixer-los ja, encara que el seu terreny natural sigui el GROUP BY de demà:

SELECT COUNT(*) AS comercials,
       STRING_AGG(nom || ' ' || cognoms, ', ' ORDER BY id) AS equip,
       ARRAY_AGG(id ORDER BY id)                           AS ids
FROM empleats
WHERE carrec = 'Comercial';
comercials equip ids
2 Óscar Peris Blasco, Laia Puig Sanchis {4,5}
  • STRING_AGG(expressió, separador) concatena els valors de totes les files en una sola cadena. L'ORDER BY intern és fonamental: sense ell, l'ordre de concatenació és arbitrari i el resultat no és reproduïble.
  • ARRAY_AGG(expressió) fa el mateix però retornant un array de PostgreSQL, útil quan l'aplicació processarà els valors per separat.

Tots dos ignoren els NULL, com la resta.

Nota de dialecte: STRING_AGG existeix a PostgreSQL i a SQL Server (2017+). MySQL i SQLite tenen GROUP_CONCAT amb sintaxi diferent, i Oracle fa servir LISTAGG. ARRAY_AGG és específic de PostgreSQL, perquè depèn que el motor tingui tipus array natius.

  1. La resolució de l'avís del mòdul 3

Arribem al plat fort. El mòdul 3 et va avisar tres vegades —a 03-02, a 03-03 i a la seva conclusió— que sumar un valor de capçalera després d'unir amb el detall infla el resultat. Ara ja tens les eines per veure-ho, mesurar-ho i arreglar-ho.

La pregunta: quant hem ingressat en total per despeses d'enviament?

El número malament

-- ⚠️ INCORRECTA: infla les despeses d'enviament
SELECT COUNT(*)                     AS files,
       SUM(co.despeses_enviament)   AS despeses_enviament_total
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id;
files despeses_enviament_total
47 278.70

El número bé

-- ✅ CORRECTA: agrega només sobre la taula de capçalera
SELECT COUNT(*)                  AS files,
       SUM(despeses_enviament)   AS despeses_enviament_total
FROM comandes;
files despeses_enviament_total
20 118.25

278,70 € davant de 118,25 €. L'error és de 160,45 €: la xifra inflada és 2,36 vegades la real, un factor molt proper al nombre mitjà de línies per comanda (47 / 20 = 2,35). No coincideix exactament perquè les comandes amb més línies no són les que més ports paguen, però l'ordre de magnitud de l'error sempre el marca aquella proporció.

Per què passa, amb la comanda 1 a la vista

SELECT co.id AS comanda_id,
       co.despeses_enviament,
       lc.id AS linia_id,
       lc.producte_id
FROM comandes AS co
JOIN linies_comanda AS lc ON lc.comanda_id = co.id
WHERE co.id = 1
ORDER BY lc.id;
comanda_id despeses_enviament linia_id producte_id
1 4.95 1 1
1 4.95 2 2
1 4.95 3 14

Els 4,95 € que BotigaVerda va cobrar una sola vegada apareixen en tres files, perquè la comanda té tres línies. SUM no sap que són el mateix cobrament repetit: suma el que veu, 14,85 € per a aquesta comanda. I no hi ha cap error, cap avís: només un número equivocat en un informe de direcció.

flowchart TD
    A["comandes<br/>20 files · 118.25 € de ports"] --> B["JOIN linies_comanda"]
    B --> C["47 files<br/>cada despesa d'enviament repetida<br/>tantes vegades com línies"]
    C --> D["SUM(co.despeses_enviament)<br/>= 278.70 €<br/>❌ inflat"]
    A --> E["SUM(despeses_enviament)<br/>sense unir amb el detall<br/>= 118.25 €<br/>✅ correcte"]

Les dues formes correctes de plantejar-ho

Forma 1 (la d'avui): agregar cada cosa al seu nivell de granularitat.

Si la pregunta és sobre capçaleres, agrega la taula de capçaleres. Si és sobre línies, agrega les línies. Dues consultes separades, cadascuna amb la seva granularitat natural:

-- Facturació de producte: nivell LÍNIA (47 files)
SELECT ROUND(SUM(quantitat * preu_unitari * (1 - descompte)), 2) AS facturacio_producte
FROM linies_comanda;
facturacio_producte
727.95 
-- Ingressos per ports: nivell COMANDA (20 files)
SELECT SUM(despeses_enviament) AS ingressos_ports
FROM comandes;
ingressos_ports
118.25

Total facturat per BotigaVerda: 727,95 + 118,25 = 846,20 €. Aquesta és la xifra correcta, i s'obté sumant dos agregats calculats per separat, cadascun sobre la seva taula.

Forma 2 (la del mòdul 7): agregar el detall primer i unir-lo després.

Quan necessitis les dues xifres en una mateixa consulta —per exemple, el total de cada comanda amb els seus ports— la tècnica correcta consisteix a col·lapsar el detall a nivell de comanda abans d'unir-lo amb la capçalera. Això exigeix una subconsulta o una CTE, que són els mòduls 7 i 10. L'esquelet, perquè el reconeguis quan arribi:

-- Avançament del mòdul 7: no l'escriguis encara, només llegeix-lo
SELECT co.id,
       co.despeses_enviament,
       tot.import_linies,
       co.despeses_enviament + tot.import_linies AS total_comanda
FROM comandes AS co
JOIN (SELECT comanda_id,
             SUM(quantitat * preu_unitari * (1 - descompte)) AS import_linies
      FROM linies_comanda
      GROUP BY comanda_id) AS tot ON tot.comanda_id = co.id;

La idea clau: la subconsulta redueix les 47 línies a 20 files, una per comanda. En unir-la amb comandes ja no hi ha multiplicació, i co.despeses_enviament apareix una sola vegada per comanda. És la solució general al problema, i per això 07-01 comença justament aquí.

Com detectar-ho tu mateix, sempre

Comprovació Com
Compta les files abans d'agregar Si el teu FROM amb JOIN retorna 47 files i estàs sumant una columna de comandes, l'estàs sumant 47 vegades
Compara amb l'agregat directe SUM(despeses_enviament) FROM comandes és la veritat de referència. Si la teva consulta complexa no hi coincideix, tens multiplicació
Pregunta't a quin nivell viu cada columna despeses_enviament viu a la comanda; quantitat viu a la línia. Sumar-les juntes exigeix portar-les abans al mateix nivell
Fes servir COUNT(DISTINCT co.id) Si és menor que COUNT(*), les capçaleres estan repetides

Aquesta última comprovació, aplicada aquí:

SELECT COUNT(*)               AS files,
       COUNT(DISTINCT co.id)  AS comandes_reals
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id;
files comandes_reals
47 20

47 ≠ 20: hi ha multiplicació. Tan bon punt vegis aquella desigualtat, ja saps que no pots sumar cap columna de comandes sense corregir-la.

Errors habituals i consells

  • Sumar una columna de capçalera després d'unir amb el detall. L'error d'aquesta lliçó: 278,70 € en lloc de 118,25 €. Agrega cada taula al seu nivell.
  • Confondre COUNT(*), COUNT(columna) i COUNT(DISTINCT columna). 20, 10 i 3 sobre la mateixa columna. Tria segons la pregunta, no per costum.
  • Fer servir COUNT(columna) creient que compta files. Compta valors no nuls. Si la columna admet nuls, et falten files.
  • Oblidar que AVG ignora els nuls. 27 420 € davant de 13 710 €. Pregunta't sempre si la mitjana és sobre les files amb dada o sobre totes.
  • Esperar 0 d'un SUM sense files. Retorna NULL. COALESCE(SUM(...), 0) a 06-04.
  • Posar un agregat al WHERE. ERROR: aggregate functions are not allowed in WHERE. És HAVING (04-06).
  • Barrejar una columna solta amb un agregat. column ... must appear in the GROUP BY clause. És GROUP BY (04-05).
  • Calcular l'import amb p.preu en lloc de lc.preu_unitari. Reescriu la història comercial: 2,50 € de més a BotigaVerda, milers en un catàleg real.
  • Arrodonir abans d'agregar. SUM(ROUND(x, 2)) acumula l'error de cada arrodoniment. Calcula amb precisió completa i arrodoneix el resultat final.
  • Suposar que AVG d'un enter retorna decimals a tots els motors. SQL Server trunca. Converteix explícitament si el codi ha de viatjar.
  • Consell: escriu primer la consulta sense agregar i compta les files. Si el recompte no és el que esperes, l'agregat que hi posis a sobre estarà malament encara que la sintaxi sigui perfecta.
  • Consell: valida cada xifra nova contra una que ja coneguis. 727,95 € de facturació ha de quadrar amb 603,52 € del 2025 més 124,43 € del 2026. Si no quadra, hi ha files de més o de menys.
  • Consell: fes servir FILTER per reunir diverses mètriques en una sola passada. És més ràpid que llançar cinc consultes i més llegible que cinc CASE niats.

Exercicis

Exercici 1

Direcció demana un quadre de comandament d'una sola fila amb aquestes sis mètriques sobre comandes:

  1. Nombre total de comandes.
  2. Nombre de clients diferents que han comprat.
  3. Nombre de comandes lliurades.
  4. Ingressos totals per despeses d'enviament.
  5. Data de la primera comanda i de l'última.
  6. Nombre de comandes del canal web (sense comercial).

Escriu una única consulta. Després respon: per què el punt 2 no et pot donar els 15 clients de la taula clients?

Exercici 2

Sobre ressenyes, calcula el nombre de ressenyes, la puntuació mitjana arrodonida a dos decimals, la pitjor i la millor puntuació, i quants productes diferents han estat ressenyats.

Després respon aquestes dues preguntes i justifica-les amb números:

  1. Quants productes del catàleg no tenen cap ressenya? Ho pots obtenir amb aquesta mateixa consulta?
  2. Si calculessis AVG(puntuacio) sobre un LEFT JOIN de productes amb ressenyes, sortiria la mateixa mitjana? Per què?

Exercici 3

El director financer vol el total facturat per BotigaVerda el 2025, despeses d'enviament incloses. Un becari li lliura això:

-- ⚠️ INCORRECTA
SELECT ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))
             + SUM(co.despeses_enviament), 2) AS total_2025
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id
WHERE co.data_comanda >= DATE '2025-01-01'
  AND co.data_comanda <  DATE '2026-01-01';
  1. Executa la consulta i digues quin número dona.
  2. Explica exactament quina part està malament i per què.
  3. Calcula el número correcte amb les dues consultes separades que hi corresponen.
  4. Indica de quant és l'error, en euros i en percentatge.

Solucions

Solució 1

SELECT COUNT(*)                                     AS comandes,
       COUNT(DISTINCT client_id)                    AS clients_compradors,
       COUNT(*) FILTER (WHERE estat = 'lliurat')    AS lliurats,
       SUM(despeses_enviament)                      AS ingressos_ports,
       MIN(data_comanda)                            AS primera_comanda,
       MAX(data_comanda)                            AS ultima_comanda,
       COUNT(*) FILTER (WHERE empleat_id IS NULL)   AS canal_web
FROM comandes;
comandes clients_compradors lliurats ingressos_ports primera_comanda ultima_comanda canal_web
20 12 14 118.25 2025-03-04 2026-02-21 10

Per què el punt 2 dona 12 i no 15: perquè la consulta parteix de comandes, i a comandes no existeix cap fila el client_id de la qual sigui 13, 14 o 15. La Núria, l'Hugo i la Inés no han comprat mai, així que no hi ha res per comptar. COUNT(DISTINCT client_id) respon a "quants clients han comprat?", no a "quants clients tenim?". Per a això segon cal preguntar-ho a clients:

SELECT COUNT(*) AS clients_registrats FROM clients;
clients_registrats
15

És el mateix aprenentatge de 03-02: la taula des de la qual parteixes determina quines preguntes pots respondre.

Solució 2

SELECT COUNT(*)                     AS ressenyes,
       ROUND(AVG(puntuacio), 2)     AS puntuacio_mitjana,
       MIN(puntuacio)               AS pitjor,
       MAX(puntuacio)               AS millor,
       COUNT(DISTINCT producte_id)  AS productes_ressenyats
FROM ressenyes;
ressenyes puntuacio_mitjana pitjor millor productes_ressenyats
12 4.08 2 5 9

12 ressenyes sobre 9 productes diferents: tres productes (l'oli, l'arròs i la crema facial) en tenen dues cadascun.

1. Els productes sense ressenya són 11: els 20 del catàleg menys els 9 ressenyats. Però no ho pots obtenir amb aquesta consulta, perquè parteix de ressenyes i els productes sense ressenya no hi apareixen — és literalment el problema de l'INNER JOIN de 03-02. Cal preguntar-ho a productes amb un anti-join, com a la solució 1 de 03-03:

SELECT COUNT(*) AS productes_sense_ressenya
FROM productes AS p
LEFT JOIN ressenyes AS r ON r.producte_id = p.id
WHERE r.id IS NULL;
productes_sense_ressenya
11

9 + 11 = 20. Tanca.

2. Sí, sortiria exactament la mateixa mitjana: 4.08. I aquest és un resultat que sorprèn. Amb productes LEFT JOIN ressenyes obtindries 23 files (les 12 ressenyes més els 11 productes sense cap), però en aquelles 11 files extra r.puntuacio és NULL, i AVG ignora els nuls. Continua sumant 49 i dividint entre 12.

SELECT COUNT(*)                    AS files,
       COUNT(r.puntuacio)          AS puntuacions,
       ROUND(AVG(r.puntuacio), 2)  AS mitjana
FROM productes AS p
LEFT JOIN ressenyes AS r ON r.producte_id = p.id;
files puntuacions mitjana
23 12 4.08

23 files, 12 valors, la mateixa mitjana. És la secció 5 en estat pur: el LEFT JOIN va canviar el nombre de files però no el conjunt de valors agregats. Si el que volguessis fos "la puntuació mitjana del catàleg tractant els productes sense ressenya com un 0", ho hauries de dir explícitament amb COALESCE (06-04) — i seria una mètrica força discutible.

Solució 3

1. Quin número dona:

-- ⚠️ INCORRECTA
SELECT ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))
             + SUM(co.despeses_enviament), 2) AS total_2025
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id
WHERE co.data_comanda >= DATE '2025-01-01'
  AND co.data_comanda <  DATE '2026-01-01';
total_2025
822.57

2. Què està malament. El primer SUM és correcte: quantitat, preu_unitari i descompte viuen a linies_comanda, que és justament la granularitat del FROM. Suma les 40 línies del 2025 i dona 603,52 €.

El segon SUM és incorrecte: despeses_enviament viu a comandes, i després del JOIN cada comanda apareix tantes vegades com línies tingui. Les 16 comandes del 2025 s'han convertit en 40 files, així que els seus ports s'han comptat 40 vegades en lloc de 16. En comptes de 85,95 € dona 219,05 €.

3. El càlcul correcte, amb dues consultes a les seves granularitats respectives:

-- Producte: nivell LÍNIA
SELECT COUNT(*) AS linies,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio_producte_2025
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id
WHERE co.data_comanda >= DATE '2025-01-01'
  AND co.data_comanda <  DATE '2026-01-01';
linies facturacio_producte_2025
40 603.52 
-- Ports: nivell COMANDA
SELECT COUNT(*)                AS comandes,
       SUM(despeses_enviament) AS ports_2025
FROM comandes
WHERE data_comanda >= DATE '2025-01-01'
  AND data_comanda <  DATE '2026-01-01';
comandes ports_2025
16 85.95

Total correcte del 2025: 603,52 + 85,95 = 689,47 €.

4. L'error.

Concepte Xifra del becari Xifra correcta Diferència
Facturació de producte 603.52 603.52 0.00
Despeses d'enviament 219.05 85.95 +133.10
Total 2025 822.57 689.47 +133.10

133,10 € de més, un 19,3 % sobre el total correcte. I observa què té de perillós el cas: la xifra inflada no és absurda —no són deu milions, és un número plausible que ningú no qüestionaria en una reunió—. Aquest és el motiu pel qual el mòdul 3 va insistir tres vegades: aquest error no es detecta llegint el resultat, només es detecta entenent la granularitat.

Conclusió

Ja saps convertir files en xifres:

  • Una funció d'agregació rep moltes files i retorna un valor. Sense GROUP BY col·lapsa tota la taula en una sola fila, fins i tot si el WHERE no en deixa passar cap.
  • Les cinc funcions: COUNT, SUM, AVG, MIN i MAX. MIN i MAX funcionen sobre qualsevol tipus ordenable, incloses dates i text.
  • Les tres formes de COUNT donen tres números diferents sobre la mateixa columna: COUNT(*) = 20 comandes, COUNT(empleat_id) = 10 amb comercial, COUNT(DISTINCT empleat_id) = 3 comercials. És la millor prova que els agregats ignoren els NULL i que COUNT(*) n'és l'única excepció.
  • Les xifres mestres de BotigaVerda: 47 línies, 113 unitats, 727,95 € de facturació de producte, 118,25 € de ports, 846,20 € de total. Per anys: 603,52 € el 2025 i 124,43 € el 2026.
  • AVG ignora els nuls, i això pot ser el que vols o exactament el contrari: 27 420 € dividint entre 10 valors, 13 710 € dividint entre 20 files. Les dues xifres són correctes per a preguntes diferents.
  • Sobre el conjunt buit, COUNT retorna 0 però SUM, AVG, MIN i MAX retornen NULL. La categoria 6 de BotigaVerda ho demostra.
  • AVG retorna NUMERIC a PostgreSQL i per això mostra setze decimals; SQL Server, en canvi, trunca la mitjana d'una columna entera.
  • No pots barrejar una columna solta amb un agregat: column ... must appear in the GROUP BY clause. Aquell error és la porta d'entrada a la lliçó següent.
  • FILTER (WHERE ...) reuneix diverses mètriques en una sola passada; el seu equivalent portable és un CASE dins de l'agregat (06-05). STRING_AGG i ARRAY_AGG agreguen text i arrays.
  • L'avís del mòdul 3 queda resolt: SUM(co.despeses_enviament) després d'unir amb linies_comanda dona 278,70 € en comptes de 118,25 €, perquè cada port es repeteix tantes vegades com línies tingui la comanda. Les dues solucions són agregar cada taula al seu nivell o col·lapsar el detall abans d'unir (mòdul 7).

A la lliçó següent, agregar dades amb GROUP BY, faràs el salt que converteix tot això en anàlisi de debò. En comptes d'una xifra per a tota l'empresa, obtindràs una xifra per cada grup: vendes per categoria, comandes per estat, clients per país, unitats per producte. Ampliarem per fi el diagrama de l'ordre lògic d'execució amb GROUP BY i HAVING entre WHERE i SELECT —ho vam prometre a 02-01—, veuràs per què els NULL formen el seu propi grup, i descobriràs per què un INNER JOIN amaga la categoria 6 mentre que un LEFT JOIN amb COUNT(columna) la mostra amb un honest 0.

◄ Anterior
◄
Següent ►
►

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats