Query sql complessa

[mrkrabs]

Query sql complessa

Ciao a tutti,

ho bisogno di una mano riguardo alla seguente query:

Select *,ROUND(avg(p.voto),2) as votodue,ROUND(avg(s.voto),2) as vototre
FROM products p JOIN products s ON p.name=s.name
WHERE p.tipo="Orale" and s.tipo="Scritto"
GROUP BY name

la mia tabella è formata da:
_id name tipo data voto quadrimestre

ora la select che ho scritto seleziona la media voti degli orali, la media voto degli scritti.
Però il mio problema è che se NON ho nella tabella entrambi i voti scritto ed orale, non viene selezionata nessuna riga.
Voglio che anche se ho come voto solo l'orale, mi venga restituita la riga cosi:

mediavotoorale mediavotoscritto
7

cioè con il campo mediavotoscritto nullo

come posso fare?
grazie

[magicale]

Ciao,
con una JOIN come hai fatto tu, non so se sia possibile, o almeno, non mi è venuto in mente nulla.

Così invece dovrebbe funzionare:

SELECT * , 
ROUND( SUM( IF( tipo="Orale" , voto, 0 ) ) / SUM( if( tipo="Orale", 1, 0 ) ) , 2 ) AS votodue, 
ROUND( SUM( IF( tipo="Scritto" , voto, 0 ) ) / SUM( if( tipo="Scritto", 1, 0 ) ) , 2 ) AS vototre 
FROM products GROUP BY nome

Capisco che avere una singola query che fa tutto possa apparir comodo... ma fare due query, una per tipo di voto, non è peccato...

[gorka]

Se ho capito bene il problema, puoi affrontarlo con un IFNULL http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

[mrkrabs]

Ciao e grazie per avermi risposto,

allora ho provato con il tuo metodo ma ottengo un errore.

quindi ho provato a semplificare un po le cose.

questa è la query che mi hai proposto...l'ho provata ma non va

SELECT *,SUM(IF(tipo="Orale",voto,0))/SUM(if(tipo="Orale",1,0)) AS votodue FROM products GROUP BY name

poi ho provato questa che funziona

SELECT *,AVG(voto) AS votodue FROM products WHERE tipo="Orale" GROUP BY name

se è presente un voto orale non dovrebbe tornare la stessa cosa? perchè la prima non va?

Praticamente ciò che voglio ottenere è una tabella con

materia mediavotiorali mediavotiscritti

utilizzando 1 sola query

[magicale]

@MrKrabs said:

allora ho provato con il tuo metodo ma ottengo un errore.
Che errore ti da?

[mrkrabs]

Si utilizzo questa select sql come linguaggio ospite del java per ottenere dei dati dal db.
Intedevo che il programma in java mi da un errore quando deve eseguire questa query. l'errore però nn è specificato, si interrompe il programma e basta.

Un appunto se è importante non so, ma utilizzo un DatabaseSQLite

[magicale]

Si, era un appunto importante
SQLite non ha la funzione IF, ma ha CASE

Al posto di

IF(tipo="Orale",voto,0)

dovrebbe essere

CASE WHEN (tipo="Orale") THEN voto ELSE 0 END

e gli altri IF, van modificati allo stesso modo.

Edit:
MySql (almeno la mia versione/configurazione) ritorna NULL quando trova una divisione per 0, SQLite non so.

[mrkrabs]

SIIIIIIIII!!!!! non finirò mai di ringraziarti!!!
grazie mille...sono 3gg che impazzisco!

SELECT *,ROUND( SUM( CASE WHEN (tipo=\"Orale\") THEN voto ELSE 0 END ) / SUM( CASE WHEN (tipo=\"Orale\") THEN 1 ELSE 0 END ) , 2 ) AS orale,ROUND( SUM( CASE WHEN (tipo=\"Scritto\") THEN voto ELSE 0 END ) / SUM( CASE WHEN (tipo=\"Scritto\") THEN 1 ELSE 0 END ) , 2 ) AS scritto FROM products GROUP BY name

Un ultima cosa dato che tra case, when, then, else, end non ci capisco nulla.

se volessi la media di tutti voti scritti ed orali?
va bene cosi? oppure correggimi se sbaglio

ROUND( SUM( CASE voto ELSE 0 END ) / SUM( CASE 1 ELSE 0 END ) , 2 ) AS mediatotale

Ancora grazie mille!

[magicale]

Se vuoi la media di tutto, senza distinzione tra scritto e orale, ti basta ROUND ( AVG(voto) , 2)

[mrkrabs]

Ciao magicale,
a distanza di tempo ho un'altra domandina su questa query, dato che ho notato uno strano comportamento.

Possiamo considerare solo questa selezione.

SELECT *,ROUND( SUM( CASE WHEN (tipo=\"Orale\") THEN voto ELSE 0 END ) / SUM( CASE WHEN (tipo=\"Orale\") THEN 1 ELSE 0 END ) , 2 ) AS orale FROM products GROUP BY name

ho notato che ad esempio la media tra 6 e 7 da come risultato 6 senza cifre dopo la virgola.

Invece la media tra 6.5 e 7 da come risultato 6.75.

questo è la stranezza...mi chiedo perchè nella media tra 6 e 7 non si ha come risultato 6.5 o 6.50?

grazie