Database azienda: query con funzioni di aggregazione

Richiesta: Elenco dei fornitori a cui è stato pagato un totale superiore alla media dei totali

Si vuol ottenere l'elenco dei fornitori, con il relativo totale degli importi pagati, che risulti superiore alla media dei totali degli importi pagati a tutti i fornitori.

Di seguito è riportato lo schema (parziale) E/R prodotto da phpmyadmin; Nella tabella fornitori sono presenti 122 items di cui riportiamo una piccola parte. Nella tabella pagamenti sono presenti 273 items di cui riportiamo una dozzina.

La query da svolgere non è banale! la risolveremo in 3 passi successivi:

1. Calcoleremo la somma totale pagata a ciascun fornitore
2. Calcoleremo la media partendo dalla somma calcolata al passo 1
3. Mostreremo i fornitori e la somma calcolata al passo 1 solo se la somma è maggiore della media calcolata al passo 2

Passo 1: calcoliamo il totale pagato a ciascun fornitore.

select codicefornitore,sum(importo) as "totale"
from pagamenti
group by codicefornitore

che produce il seguente risultato (mostrato solo in parte):

Notare che solo 98 fornitori hanno ricevuto almeno un pagamento (per cui ci sono 24 fornitori che non hanno alcun pagamento)

Passo 2: calcoliamo la media dei pagamenti (su 98 fornitori).

select avg(t1.totale) as media
FROM(
	select codicefornitore,sum(importo) as "totale"
	from pagamenti
	group by codicefornitore
) as t1

Si tratta di una query composta: quella più interna calcola la somma per ciascun fornitore e produce la tabella mostrata in precedenza chiamata t1; proprio perché il risultato è una tabella può essere usata tranquillamente nel FROM della query più esterna. La query più esterna quindi, partendo dalla tabella t1, calcola la media dei pagamenti effettuati il cui risultato è:

Notare che, essendo il risultato formato da una sola riga e da una sola colonna, è come se fosse un numero e può essere usato come termine di confronto in una query (che è quello che facciamo nella query che segue dopo la clausola having)

Passo 3: infine si passa a produrre l'elenco dei fornitori che hanno ricevuto un pagamento maggiore della media.

select nomefornitore, sum(importo) as "importo pagato"
from pagamenti p join fornitori f on idfornitore=codicefornitore
group by idfornitore
having sum(importo)>
(
	select avg(t1.totale) as media
	FROM(
		select codicefornitore,sum(importo) as "totale"
		from pagamenti
		group by codicefornitore
		) as t1
)

in questa query, dando per acquisito il significato di sum(importo) e group by idfornitore, mostriamo (SELECT) il nome del fornitore (preso dalla tabella fornitori), l'importo totale ricevuto (calcolato dalla tabella pagamenti), ma solo se tale importo è maggiore della media calcolata al passo 2 (having sum(importo)> ...)

Nota: poiché le tabelle coinvolte sono 2 è necessario nel FROM effettuare un JOIN tra esse specificando nella clausola ON il criterio che i record della prima e della seconda tabella devono soddisfare per poter essere uniti.