[MYSQL] query complessa con count

[strae]

ciao, io ho una struttura di questo tipo (abbreviata, tolgo i dati che non servono):

tabella prev:
id data
1 0808
2 0808
3 0809
4 0809
5 0810
6 0810

tabella pay:
id idPrev stato
1 1 0
2 1 0
3 1 1
4 2 0
5 2 1
6 2 1
7 3 1
8 3 1
9 4 1
10 4 1
11 5 1
12 5 1
13 5 1
14 6 0

dove, in pratica, pay.stato indica se il pagamento è ricevuto si o no.
La query che mi serve a me, dovrebbe restituirmi le date raggruppate (annomese decorrenza), due colonne:
una con il numero di fatture con TUTTI i pagamenti a stato = 1, l'altra con il numero delle fatture che non hanno tutti i pagamenti conclusi.

con i dati sopra, dovrei ottenere questo risultato:

data | fattureOK | fattureNo |
0810 | 1 | 1 |
0809 | 2 | 0 |
0808 | 0 | 2 |

come cavolo faccio?
è possibile?

io ho fatto delle prove:

SELECT
  prev.id,
 ( SELECT COUNT(pay.id) FROM pay WHERE pay.stato = '0' AND pay.idPrev = prev.id ) AS payNo,
 ( SELECT COUNT(pay.id) FROM pay WHERE pay.stato = '1' AND pay.idPrev = prev.id ) AS payOk,
 ( SELECT COUNT(pay.id) FROM pay WHERE pay.idPrev = prev.id ) AS payTOT
FROM prev
INNER JOIN pay ON (pay.idPrev = prev.id)
GROUP BY prev.id 

ma così ho riga per riga le fatture con il numero di pagamenti conclusi, da concludere e il totale (il raggruppamento per data mi sfracella i dati, mettendolo in quella query)

ho provato con:

SELECT
  prev.data,
 ( SELECT
  COUNT(prev.id)
  FROM prev
  HAVING ( SELECT COUNT(pay.id) FROM pay WHERE pay.stato = '1' AND pay.idPrev = prev.id ) = ( SELECT COUNT(pay.id) FROM pay WHERE pay.idPrev = prev.id )
 ) AS fattureOk,
 ( SELECT
  COUNT(prev.id)
  FROM prev
  HAVING ( SELECT COUNT(pay.id) FROM pay WHERE pay.stato = '0' AND pay.idPrev = prev.id ) != 0
 ) AS fattureNo
FROM prev
INNER JOIN pay ON (pay.idPrev = prev.id)
GROUP BY prev.data 

ma mi restituisce
data fattureOk fattureNo
0808 NULL 6
0809 6 NULL
0810 6 NULL

idee?

[nicola75ss]

Sicuramente mi sfugge qualcosa ma non riesco a capire come fai a mettere in relazione pay.idprev con prev.id

Attenendomi al tuo esempio con data 0810, se hai 3 record nella tabella pay con idprev = 5 per quale motivo il risultato dovrebbe essere pari a 1 0k e 1 no?

[strae]

ogio fattura (tabella prev) può avere più di un pagamento

Attenendomi al tuo esempio con data 0810, se hai 3 record nella tabella pay con idprev = 5 per quale motivo il risultato dovrebbe essere pari a 1 0k e 1 no?

appunto, la fattura con id 5 mette a 1 la colonna fattureOK, la fattura id 6 mette a 1 la colonna fattureNo (non avendo tutti i pagamenti conclusi)

data | fattureOK | fattureNo |
0810 | 1 | 1 |

negli esempio che ho scritto io riesco solo a contare il numero di pagamenti ok e no per ogni data, non riesco per ogni data a contare tutte le fatture 'concluse' e quelle 'non concluse'

[nicola75ss]

Originariamente inviato da strae
appunto, la fattura con id 5 mette a 1 la colonna fattureOK, la fattura id 6 mette a 1 la colonna fattureNo (non avendo tutti i pagamenti conclusi)

Ah, ok. Adesso ho capito.
Vedo se riesco a trovare una soluzione.

[strae]

Originariamente inviato da nicola75ss
Ah, ok. Adesso ho capito.
Vedo se riesco a trovare una soluzione.

eh.. quello che mi serve, alla fine, è sapere quante fatture 'concluse' e quante 'non concluse' ho per ogni mese dell'anno...

io per ora ho risolto facendo prima una semplicissma select group by data, poi, per ogni data cerco le fatture.

sono 2 query distinte e due cicli while che mi piacerebbe mettere in uno..

[nicola75ss]

Prova così

select data, 
sum(if(state=1,1,0)) as ok,
sum(if(state=0,1,0)) as non
from 
(select idprev,
if(count(stato)-sum(if(stato=1,1,0))>0,0,1) as state
from pay
group by idprev) as pay
inner JOIN prev ON (pay.idPrev = prev.id)
group by prev.data

[strae]

Originariamente inviato da nicola75ss
Prova così

select data, 
sum(if(state=1,1,0)) as ok,
sum(if(state=0,1,0)) as non
from 
(select idprev,
if(count(stato)-sum(if(stato=1,1,0))>0,0,1) as state
from pay
group by idprev) as pay
inner JOIN prev ON (pay.idPrev = prev.id)
group by prev.data

wow, funziona!
grazie mille..

senti, alcuni passaggi non sono molto chiari.. sei già stato gentilissimo e non vorrei abusare, ma potresti spiegare la query?

[nicola75ss]

select idprev,
if(count(stato)-sum(if(stato=1,1,0))>0,0,1) as state
from pay
group by idprev

Con questa query, dalla tabella pay, seleziono ogni singolo idprev, conto quanti record ci sono per ciascuno di essi,quanti hanno stato =1 ed eseguo la differenza.
A seconda che la differenza sia maggiore o uguale a zero assegno a ciascun idprev valore 0 oppure 1. A questo punto faccio una join con l'altra tabella, raggruppo per data e conto quante hanno stato zero oppure uno. Spero di essere stato chiaro.

[strae]

Originariamente inviato da nicola75ss

select idprev,
if(count(stato)-sum(if(stato=1,1,0))>0,0,1) as state
from pay
group by idprev

Con questa query, dalla tabella pay, seleziono ogni singolo idprev, conto quanti record ci sono per ciascuno di essi,quanti hanno stato =1 ed eseguo la differenza.
A seconda che la differenza sia maggiore o uguale a zero assegno a ciascun idprev valore 0 oppure 1. A questo punto faccio una join con l'altra tabella, raggruppo per data e conto quante hanno stato zero oppure uno. Spero di essere stato chiaro.

si mi sono spezzettato la tua query e ho visto il funzionamento dello state.. solo non mi è chiaro l'utilizzo della subquery DOPO il FROM.. cioè non mi è chiaro come si comporta la query..

[nicola75ss]

Allora, nella query interna ho eseguito la differenza tra il numero di record e quelli che avevano valore pari a 1. Ho assegnato a state valore 1 per tutti i record per i quali era stato pagato tutto e valore 0 per quelli in cui c'è ancora qualcosa da pagare.
A questo punto ho fatto una semplice join come avevi già tentato di fare tu e nella query esterna ho messo queste due righe

sum(if(state=1,1,0)) as ok,
sum(if(state=0,1,0)) as non

che non fanno altro che verificare il valore di state ricavato in precedenza. Quando è pari ad 1 incrementa di una unità il valore delle fatture evase, quando invece state precedentemente è risultato pari a 0 allora incremento il numero di fatture inevase. Tutto ciò ovviamente raggruppando per data come avevi richiesto.