[SQL] Query con doppio count: non funge

[Donbabbeo]

Premessa: sto svolgendo un piccolo progetto riguardante un magazzino e sono in procinto di creare la query che in base al dipendente da me scelto, ne estrae lo stipendio totale andando a pescare i valori dalle varie tabelle.

Questa è la parte, presa singolarmente che si preoccupa di calcolare la base fissa dello stipendio, che varia in base alle ore:

SELECT dipendenti.nome, dipendenti.cognome, count(lavora.iddipendente) * contratti.costoorario 
FROM dipendenti, contratti, stipula, lavora
WHERE dipendenti.iddipendente = 1 AND lavora.iddipendente = 1
AND stipula.iddipendente = 1 AND stipula.idcontratto = contratti.idcontratto;

prende i dati del dipendente dalla tabella dipendenti, le ore di lavoro da lavora e il costo orario dalla tabella contratti, stipula è l'associazione tra dipendenti e contratti. Il dipendente 1, Mario Bianchi, ha lavorato 6 ore e dal suo contratto percepisce 1 euro all'ora. Difatti il risultato della query è questo:

+-------+---------+----------------------------------------------------+
| nome  | cognome | count(lavora.iddipendente) * contratti.costoorario |
+-------+---------+----------------------------------------------------+
| Mario | Bianchi |                                               6.00 |
+-------+---------+----------------------------------------------------+

La parte, presa singolarmente che si occupa di calcolare il bonus stipendio è questa:

SELECT dipendenti.nome, dipendenti.cognome, count (consegna.idfattorino) * contratti.bonusconsegna
FROM dipendenti, consegna, contratti, stipula
WHERE dipendenti.iddipendente = 1 AND stipula.iddipendente = 1
AND consegna.idfattorino = 1 AND stipula.idcontratto = contratti.idcontratto;

Effettua un lavoro praticamente identico alla precedente, solo che effettua un diverso calcolo, considerando il numero di consegne effettuate e moltiplicandole per il valore nel contratto. Il risultato, considerando che 'sto tizio ha fatto 2 consegne e prende 1 euro a consegna è:

+-------+---------+----------------------------------------------------+
| nome  | cognome | count(consegna.idfattorino) * contratti.bonusconsegna |
+-------+---------+----------------------------------------------------+
| Mario | Bianchi |                                               2.00 |
+-------+---------+----------------------------------------------------+

A me purtroppo interessava più che altro avere il TOTALE direttamente su quanto questo povero Cristo prende... Quindi ho creato una query di questo tipo:

SELECT dipendenti.nome, dipendenti.cognome, count(lavora.iddipendente) * contratti.costoorario + count(consegna.idfattorino) * contratti.bonusconsegna AS Stipendio
FROM dipendenti, lavora, stipula, contratti, consegna
WHERE dipendenti.iddipendente = 1 AND lavora.iddipendente = 1
AND stipula.iddipendente = 1 AND consegna.idfattorino = 1
AND stipula.idcontratto = contratti.idcontratto;

Mi sarei aspettato come risultato del campo "Stipendio" il valore corretto, cioè 8 €, purtroppo la realtà è ben diversa, ottenendo invece 24 €...
Se provo a separare il count, utilizzando entrambi i campi nella stessa query ottengo per ognuno 12 €. Qualcuno sa da cosa possa derivare l'errore?
Grazie.

[gugoXX]

Ci sono 2 problemi.
1.
Probabilmente stai usando un qualche Motore SQL rilassato non standard (MySQL?)
Stai infatti usando una funzione di gruppo (COUNT) senza scrivere un'opportuna clausola GROUP BY.
Per prima cosa ti invito quindi ad aggiungere la clausola GROUP BY, per evitare di incorrere in problemi non deterministici dai quali non si esce piu'.

la cosa migliore sarebbe dedurre i dati che servono lavorando sulla chiave della persona, per poi andare in JOIN con le anagrafiche per prendere nomi, cognomi etc.

2.
La COUNT(campo) semplicemente conta quante righe hanno "campo" diverso da NULL. se vuoi contare quante sono le diverse occorrenze di "Campo", devi usare la
COUNT(DISTINCT campo), non supportata da tutti i motori, e per la quale c'e' pero' un costrutto standard (scomodo) che si puo' usare per supplire alla mancanza.

[Donbabbeo]

Esatto, sto usando MySQL, purtroppo sono costretto per "direttive superiori"
Innanzitutto grazie mille per la risposta, più tardi cercherò di correggere la funzione.
C'è una cosa però del tuo discorso che non ho capito:
la cosa migliore sarebbe dedurre i dati che servono lavorando sulla chiave della persona, per poi andare in JOIN con le anagrafiche per prendere nomi, cognomi etc.

Non è quello che ho fatto?
Magari in realtà si fa in altro modo, io sto seguendo solo queste mie "direttive" ... Sono curioso, se ti fosse possibile vorrei vedere un tuo esempio.

Comunque grazie

[Donbabbeo]

Ok, non picchiarmi, ma sto riprovando a modificare la query...

La count non funziona con la distinct, cioè a me serve proprio di contare tutte le occorrenze non null, visto che tramite i vari join ottengo già la scrematura delle varie righe eventualmente ripetute che possono presentarsi.

Mentre per la group by: l'ho inserita senza ottenere alcuna variazione del risultato...
Considerando l'uso di due count nella stessa query... su quale dovrei usare la group by? (Non credo sia possibile usare 2 group by nella stessa query, o perlomeno non ne ho trovato alcuna traccia sulla mia documentazione)

Il finale è sempre lo stesso: il risultato è sempre uguale, cioè non torna, sto provando tutte le modifiche possibili che il mio piccolo cervellino riesce a ponderare, ma non c'è storia

[gugoXX]

Originariamente inviato da Donbabbeo

Esatto, sto usando MySQL, purtroppo sono costretto per "direttive superiori"
Innanzitutto grazie mille per la risposta, più tardi cercherò di correggere la funzione.
C'è una cosa però del tuo discorso che non ho capito:
la cosa migliore sarebbe dedurre i dati che servono lavorando sulla chiave della persona, per poi andare in JOIN con le anagrafiche per prendere nomi, cognomi etc.

Non è quello che ho fatto?
Magari in realtà si fa in altro modo, io sto seguendo solo queste mie "direttive" ... Sono curioso, se ti fosse possibile vorrei vedere un tuo esempio.

Comunque grazie

Per poter scrivere una query corretta occorre conoscere lo schema del DB, compresi se soprattutto i constraint come le chiavi primarie.
P.Es., se ogni fattorino potesse stipulare piu' di un contratto, ciascuno con il suo bonusconsegna, cosa vorresti fare?

Se p.es ce n'e' uno solo di sicuro, perche' la chiave primaria di contratti e' IDfattorino, oppure perche' c'e' un vincolo di unicita' sopra, una delle possibili scritture potrebbe essere

Query1: (Per ottenere le ore lavorate da ciascun dipendente)

SELECT iddipendente,COUNT(*) orelavorate
FROM lavora
GROUP BY iddipendente

Query2: (Per ottenere i colli consegnati da ciascun fattorino)

SELECT idfattorino,COUNT(*) colli
FROM consegna
GROUP BY idfattorino

La JOIN completa (ove bisogna sostituire le query precedenti al posto di Query1 e Query2, ovvero aprire la parentesi tonda, scrivere la query relativa e mettere l'alias)

SELECT dipendenti.nome, dipendenti.cognome, query1.orelavorate * contratti.costoorario + query2.colli * contratti.bonusconsegna AS Stipendio
FROM dipendenti, lavora, contratti, Query1, Query2
WHERE  lavora.iddipendente = dipendenti.iddipendente
  AND query1.iddipendente =dipendenti.iddipendente
  AND query2.idfattorino =  dipendenti.iddipendente 
  AND stipula.iddipendente = dipendenti.iddipendente 
  AND stipula.idcontratto = contratti.idcontratto;

ovvero, alla fine, se il vincolo e' confermato e se non ho fatto errori

SELECT dipendente.iddipendente, dipendenti.nome, dipendenti.cognome, query1.orelavorate * contratti.costoorario + query2.colli * contratti.bonusconsegna AS Stipendio
FROM dipendenti, lavora, stipula, contratti, 
   ( SELECT iddipendente,COUNT(*) orelavorate
          FROM lavora
          GROUP BY iddipendente  ) AS Query1,
   (   SELECT idfattorino,COUNT(*) colli
           FROM consegna
          GROUP BY idfattorino
    ) AS Query2
WHERE  lavora.iddipendente = dipendenti.iddipendente
  AND query1.iddipendente =dipendenti.iddipendente
  AND query2.idfattorino =  dipendenti.iddipendente 
  AND stipula.iddipendente = dipendenti.iddipendente 
  AND stipula.idcontratto = contratti.idcontratto;

se crei una vista di quella roba li', potrai scrivere alla fine
SELECT * FROM MiaVista WHERE iddipendente=1



e se al posto di usare le JOIN cosi', usi le JOIN corrette, ovvero la sintassi

FROM dipendenti JOIN lavora ON ( lavora.iddipendente = dipendenti.iddipendente)

per tutte le join, e' ancora meglio.

[Donbabbeo]

wow, non c'è che dire, non so nulla di sql

Vedrò di fare una profonda revisione dell'intero database

[Donbabbeo]

Ti amo.

Ora funziona correttamente!

PS: nella query completa manca il group by dipendenti.iddipendente alla fine giusto?

Spero di non doverti più disturbare ma ne dubito

[gugoXX]

No, nella query completa non serve alcun Group by, essendo che non sono coinvolte funzioni di gruppo.

[Donbabbeo]

Come era ovvio, durante la revisione dell'intero database, ho trovato qualche problema in alcune funzioni che finora sono definite solo nella mia testolina bacata...

Bene o male son riuscito a passare sopra tutto, ma sembra che non riesca invece a risolvere questa particolare Query...

La situazione è "abbastanza" semplice e la descrivo con questa mini-tabellina doppia:

		PIPPO		PLUTO		PAPERINO
MARIO		5		4		1
GUIDO		10		3		1
GIACOMO		2		2		1
MARCO		3		1		5

In pratica stiamo parlando dei magazzinieri, e qui riassunti abbiamo i vari magazzinieri con le quantita di merci depositate per tipo (come si intuisce dai loro nomi sono merci di fantasia ).
Il mio desiderio era ottenere come risultato della query una tabella che mi restituisca per ogni dipendente l'elenco dei prodotti e il conto totale, ma sembra che non ne sia capace...

Posso ottenerla per un singolo dipendente (in pratica ottengo una singola riga di questa tabella a doppia entrata) ma non riesco a farlo per tutti i dipendenti contemporaneamente. Io vorrei qualcosa così:

+----------------+---------+----------+
| idmagazziniere | idmerce | count(*) |
+----------------+---------+----------+
|              1 |       1 |        5 |
|              1 |       2 |        4 |
|              1 |       3 |        1 |
|              2 |       1 |       10 |
|              2 |       2 |        3 |
|              2 |       3 |        1 |
|              3 |       1 |        2 |
|              3 |       2 |        2 |
|              3 |       3 |        1 |
|              4 |       1 |        3 |
|              4 |       2 |        1 |
|              4 |       3 |        5 |
+----------------+---------+----------+

E' possibile? Il problema sembra risiedere nel fatto di dover utilizzare l'istruzione group by, poiche se raggruppo per dipendenti otterrò 4 righe con la somma dei depositi dei vari tipi per ciascun magazziniere, mentre se raggruppo per merce ottengo il totale disponibile per ciascuna merce...

[gugoXX]

Il problema sta nella tabella. Quella sembra essere una tabella risultato di quache processo di reportistica, dato che non e' una tabella relazionale corretta.
Cosa accadrebbe infatti a quella tabella se saltasse fuori un nuovo prodotto in azienda?
Cerca se per caso esiste una tabella sorgente dei dati, che dovrebbe fra l'altro gia' essere in una forma simile a quello che desideri.

[Donbabbeo]

Innanzitutto grazie per la rapidità con cui rispondi

Ovviamente quel risultato è ottenuto dalla combinazione di 3 tabelle: la tabella dei dipendenti da cui prendo i dati sui dipendenti come nome e cognome (attualmente c'è solo l'id perchè sono pigro e finchè non è corretto non lo scrivo ), la tabella delle merci da cui prendo i dati sulle merci (nel caso specifico solo il nome, stesso discorso qui sopra, il nome ce lo segno solo quando sarà corretto) e una tabella che mi tiene in relazione i depositi, nella quale ad ogni riga associa l'id dipendente ad un id merce e indica l'ora in cui viene eseguita l'operazione. In pratica se il dipendente A deposita 5 merci B, nella tabella risulteranno 5 nuove tuple di questo tipo :

(A, B, 2009-01-12 16:18:10)

Ovviamente ciascuna nuova tupla avrà un valore di secondi differente e in questa tabella per evitare ciascuna possibile ridondanza la chiave primaria è formata dalla combinazione di tutti e 3 i valori.
Ne consegue che per calcolare le merci depositate da un dipendente non posso semplicemente pescare il numero di merci depositate, visto che tale valore non è memorizzato e quindi sono obbligato ad effettuare il count per conoscere quante merci deposita un qualsiasi magazziniere. Probabilmente non è un sistema efficiente, ma è il primo che mi è venuto in mente che si integrasse con il database

Alternativamente potrei aggiungere un campo ai dipendenti in cui tengo conto del numero di merci depositate e ad ogni nuova interazione verrebbe aggiornato tramite un trigger, ma non credo cambi poi molto, visto che farebbe una semplice "somma bruta" dei depositi di ciascun dipendente.

[magmahell]

Ciao, sarei interessato a creare una visualizzazione simile alla tua riportata qui sopra, potresti postare la query che hai utilizzato per realizzarla.

grazie!