I have a table "schLoc" like this
------------------------
| id | ... | ... | ... |
------------------------
| 1 | ... | ... | ... |
| 2 | ... | ... | ... |
| 3 | ... | ... | ... |
| 4 | ... | ... | ... |
| 5 | ... | ... | ... |
| .. | ... | ... | ... |
------------------------
and another table like this "schLocDett" like this where schLoc.id=schLocDett.idDoc
-------------------------------
| idDoc | qta | ... | merce |
-------------------------------
| 1 | 1 | ... | fattLoc |
| 1 | 1 | ... | fattSrv |
| 2 | 3 | ... | fattLoc |
| 2 | 2 | ... | notcSrv |
| 2 | 2 | ... | fattSrv |
| 3 | 5 | ... | fattSrv |
| 3 | 3 | ... | notcSrv |
| 3 | 3 | ... | fattLoc |
| 3 | 7 | ... | fattLoc |
| 4 | 5 | ... | notcSrv |
| 4 | 4 | ... | fattSrv |
| 4 | 1 | ... | fattSrv |
| 5 | 1 | ... | notcSrv |
| ... | ... | ... | ....... |
-------------------------------
I would like to have the list complete of schLoc and for each id the sum of qta associated but only for merce=fattLoc. In case that don't exist the sum will be 0. This is the result I expect
---------------------------
| id | sumQta | ... | ... |
------------------------
| 1 | 1 | ... | ... |
| 2 | 3 | ... | ... |
| 3 | 10 | ... | ... |
| 4 | 0 | ... | ... |
| 5 | 0 | ... | ... |
| .. | ... | ... | ... |
---------------------------
I try with this:
SELECT TOT.sumQta, TAB.*,
FROM schLoc AS TAB, schLocDett AS DETT,
(SELECT idDoc, SUM(qta) AS sumQta
FROM schLocDett
WHERE merce='fattLoc'
GROUP BY idDoc) AS TOT
WHERE TAB.id>0
AND TAB.id=DETT.idDoc
AND TAB.id=TOT.idDoc
but not get Id 4 and 5
Any suggest are welcome
Aucun commentaire:
Enregistrer un commentaire