[MySQL] query doppia join con group by e risultato inaspettato

[Cheope]

Ciao a tutti.
Cerco di spiegarvi il mio problema semplificando.
Ho tre tabelle, buoni_maturati, buoni_consegnati e utenti, dove memorizzo via via dei buoni sconto che uno matura e quelli che gli vengono consegnati allo scopo di fare via via un conguaglio.
La tabella utenti è in relazione uno a molti sia con buoni_maturati che con buoni_consegnati, che sono due tabelle strutturalmente identiche. Per ragioni che non sto qui a spiegare, le due tabelle devono essere separate.

Struttura di 					Struttura di 
buoni_maturati e buoni_consegnati 		utenti
----------------------------------		---------
id PK						id PK
id_utente chiave esterna			nome
data						cognome
quantita

Ho bisogno di una query che mi visualizzi per ciascun utente la somma dei buoni maturati e di quelli consegnati e la loro differenza.
Se faccio due query liscie separate, i calcoli funzionano.

SELECT U.cognome, U.nome, SUM(M.quantita) AS n_buoni_maturati 
FROM utenti AS U  INNER JOIN buoni_maturati AS M ON U.id=M.id_utente 
GROUP BY M.id_utente ORDER BY U.cognome ASC;

risultato aggiungendo clausola WHERE U.id=1 : n_buoni_maturati = 47, ok

SELECT U.cognome, U.nome, SUM(C.quantita) AS n_buoni_consegnati 
FROM utenti AS U  INNER JOIN buoni_consegnati AS C ON U.id=C.id_utente 
GROUP BY C.id_utente ORDER BY U.cognome ASC;

risultato aggiungendo clausola WHERE U.id=1 : n_buoni_consegnati = 15, ok

Se metto le due join in una query unica, fa calcoli strani e non capisco dove sbaglio. :master:

SELECT U.cognome, U.nome, SUM(M.quantita) AS n_buoni_maturati, SUM(C.quantita) AS n_buoni_consegnati
FROM (utenti AS U  INNER JOIN buoni_maturati AS M ON U.id=M.id_utente) 
	INNER JOIN buoni_consegnati AS C ON U.id=C.id_utente 
GROUP BY U.id
ORDER BY U.cognome ASC;

risultato aggiungendo clausola WHERE U.id=1 :
n_buoni_maturati = 94
n_buoni_consegnati = 510

???

Ho provato a cambiare il raggruppamento mettendo GROUP BY U.id, M.id_utente, C.id_utente, ma il risultato è identico.
In pratica moltiplica il risultato di n_buoni_maturati per 2, che è il numero di righe presenti nella tabella buoni_maturati con quell'id_utente; moltiplica il risultato dei n_buoni_consegnati per 34, che è il numero di righe presenti nella tabella buoni_consegnati con quell'id_utente;

Mi manca un passaggio fondamentale.
Eppure non mi sembra una cosa tanto complicata...

Grazie a chi avrà la pazienza di aiutarmi...

[Andy_87]

Penso che il problema sia la group by, hai provato a mettere:
GROUP BY U.cognome, U.nome, n_buoni_maturati, n_buoni_consegnati

Te lo dico perchè l'altro giorno ho fatto una cosa simile però con SQL Server, è vero che è diverso però magari provaci. Anche perchè, se non ricordo male, mi sembra che quando si usano le funzioni di aggregazione, ad esempio la SUM, i campi della select vanno messi anche nella group by o forse solo i campi aggregati? Insomma ho qualche ricordo confuso, francamente con MySql faccio raramente delle query complesse.

[nicola75ss]

select u.*,sum(if(tipologia=1,quantita,0)) as maturati,sum(if(tipologia=2,quantita,0)) as consegnati,
sum(if(tipologia=1,quantita,0)) - sum(if(tipologia=2,quantita,0)) as differenza
from utenti as u inner join (
select id_utente,quantita,1 as tipologia
from buoni_maturati
union all
select id_utente,quantita,2
from buoni_consegnati) as b
on u.id = b.id_utente
group by u.id

[Cheope]

Originariamente inviato da Andy_87
GROUP BY U.cognome, U.nome, n_buoni_maturati, n_buoni_consegnati

A parte che non si può raggruppare sugli alias, si raggruppa per i campi uguali.
La somma è di quelle righe che hanno uno stesso id_utente, non su chi ha lo stesso numero di buoni maturati. Comunque grazie dell'interessamento.

@nicola75ss
Sta pizza! E io che pensavo min mancasse solo un pezzettino di query...
Prima cerco di capire il significato, poi provo e ti faccio sapere...
Thanx

[Cheope]

@nicola75ss
sarà il caldo, ma la tua query è un po' troppo complicata per la mia fragile testolina. E poi con union i campi non si accodano?
Però mi hai dato l'idea giustissima della subquery .
Il fatto che moltiplicasse i risultati mi ha fatto capire che mancava un raggruppamento da qualche parte, ed ecco la (semplice) soluzione:

SELECT U.cognome, U.nome, SUM(M.quantita) AS maturati, 
(SELECT SUM(quantita) FROM buoni_consegnati WHERE id_utente=M.id_utente GROUP BY id_utente) AS consegnati
FROM utenti AS U INNER JOIN buoni_maturati AS M ON U.id=M.id_utente
GROUP BY U.id

Thanx!

[nicola75ss]

La mia query semplicemente fa un'unione tra le due tabelle dei buoni, aggiungendo un campo tipologia uguale a 1 per i buoni maturati e uguale a 2 per quelli consegnati.
La tabella utenti viene messa in relazione con la tabella risultante dall'unione e, a seconda della tipologia, il quantitativo viene aggiunto al totale dei buoni maturati o di quelli consegnati.

[Andy_87]

Si hai ragione, non si raggruppa sugli alias, però in SQL se fai una funzione di aggregazione, sei obbligato a fare la group by di tutti i campi della select, esclusi appunto gli alias, ma questa è un'altra storia.

[Cheope]

@nicola75ss
Grazie. Mi hai dato uno spunto importante per risolvere anche un'altra questione.

@Andy_87
Non lo sapevo. Pensavo che il raggruppamento vada fatto in base al dato che si vuole estrapolare.

Grazie ancora a tutti.