[MYSQL] probleempje

[pankmeister]

Ik heb 2 tabellen:

tabel klanten:
klantid //primaire sleutel
voornaam //voornaam van de klant
achternaam //achternaam van de klant

tabel facturen
klantid //klantid van de klant van wie de factuur is
bedrag //het bedrag van de factuur
betaald //0 als de factuur niet betaald is, 1 als de factuur wel betaald is

Wat ik zou willen is een overzicht van alle klanten met het aantal betaalde facturen en het aantal niet betaalde facturen in 1 rij.

Het is mij tot nu toe gelukt om deze 2 tabellen te koppelen:
SELECT klanten.klantid, voornaam, achternaam, betaald,
FROM `klanten`
LEFT JOIN `facturen` ON klanten.klantid = facturen.klantid
ORDER BY klanten.klantid

maar ik krijg de betaalde/niet betaalde facturen maar niet opgeteld langs elkaar.
Hoe moet ik dit doen?

 

[SvU]

Zou moeten kunnen met SUM() in combinatie met een WHERE-clausule en een GROUP BY op alle overige kolommen.

Overigens ben ik niet helemaal zeker of WHERE hier de juiste methode is, ik zou me kunnen voorstellen dat je HAVING moet gebruiken.

 

[pankmeister]

Zou moeten kunnen met SUM() in combinatie met een WHERE-clausule en een GROUP BY op alle overige kolommen.

Overigens ben ik niet helemaal zeker of WHERE hier de juiste methode is, ik zou me kunnen voorstellen dat je HAVING moet gebruiken.

Ik had geprobeerd met een sum, maar hiermee telt hij niet goed (als je vb 2 niet betaalde facturen hebt doet 0+0 en is de uitkomst dus 0 wat 2 moet zijn)

Wat ik dus eigenlijk zou willen is een resultaat in de aard van dit:

Klantid - Voornaam - Achternaam - Niet betaald - Betaald
1 jos vijslos 1 3
2 jan deman 2 0
3 romeo alfa 0 0

 

[pankmeister]

Ik ben in tussentijd wat verder geraakt:

SELECT DISTINCT klanten.klantid, voornaam, achternaam, (SELECT count (betaald)
FROM facturen
WHERE (((facturen.betaald)=0))), (SELECT count (betaald)
FROM facturen
WHERE (((facturen.betaald)=1)))
FROM `klanten`
left JOIN `facturen` ON klanten.klantid = facturen.klantid

Hiermee geeft hij het weer hoe ik het zou willen hebben (hij telt de niet betaalde en de betaalde facturen op), maar het probleem is hier dat hij het niet per klant uitteld.
Iemand enig id hoe ik dit kan oplossen?

 

[SvU]

Als het goed is moet je wel een GROUP BY-clausule hebben, kijk daar eens naar.

 

[bargeboer]

pankmeister,

De laatste suggestie van SvU begrijp ik niet, maar volgens mij moet je gewoon een extra AND expressie aan de WHERE clause van de SELECT voor de count van betaalde en niet-betaalde facturen toevoegen. Dus je krijgt dan:

SELECT DISTINCT klanten.klantid, voornaam, achternaam,
(SELECT count (betaald)
FROM facturen
WHERE (((facturen.betaald)=0) AND (facturen.klantid = klanten.klantid))),
(SELECT count (betaald)
FROM facturen
WHERE (((facturen.betaald)=1) AND (facturen.klantid = klanten.klantid)))
FROM `klanten` LEFT JOIN `facturen` ON klanten.klantid = facturen.klantid

Ik heb hier zelf geen MYSQL, maar met MS Access werkt dit .

 

[pankmeister]

pankmeister,

De laatste suggestie van SvU begrijp ik niet, maar volgens mij moet je gewoon een extra AND expressie aan de WHERE clause van de SELECT voor de count van betaalde en niet-betaalde facturen toevoegen. Dus je krijgt dan:

SELECT DISTINCT klanten.klantid, voornaam, achternaam,
(SELECT count (betaald)
FROM facturen
WHERE (((facturen.betaald)=0) AND (facturen.klantid = klanten.klantid))),
(SELECT count (betaald)
FROM facturen
WHERE (((facturen.betaald)=1) AND (facturen.klantid = klanten.klantid)))
FROM `klanten` LEFT JOIN `facturen` ON klanten.klantid = facturen.klantid

Ik heb hier zelf geen MYSQL, maar met MS Access werkt dit .

Het werkt! Bedankt! Kan je mij misschien even uitleggen waarom dat er AND (facturen.klantid = klanten.klantid) bij moet? Vermits niet van iedere klantid een factuur is gemaakt kan ik hier even niet volgen hoe dat het komt dat hij toch alle klanten pakt

 

[bargeboer]

Het werkt! Bedankt! Kan je mij misschien even uitleggen waarom dat er AND (facturen.klantid = klanten.klantid) bij moet? Vermits niet van iedere klantid een factuur is gemaakt kan ik hier even niet volgen hoe dat het komt dat hij toch alle klanten pakt

Je vraagt eigenlijk twee dingen:
1. waarom AND (facturen.klantid = klanten.klantid)
Je moet deze SELECT als een op zichzelf staande query zien waarbij het resultaat in een veld ingevuld wordt. Als je die uitvoert, dan vraag je uit de tabel facturen eenvoudig alle rijen waarbij de waarde voor betaald op 0 of 1 staat. Dus zonder te selecteren op klantid telt deze query alle records in de tabel facturen met de voorwaarde die je gesteld hebt. Als de query wordt uitgevoerd, wordt dit record voor record gedaan in de klanten tabel. Door de extra voorwaarde voor klantid toe te voegen kijkt de query naar de klantid van het actuele record in de klanten tabel en selecteerd alleen die records uit facturen die bij het klantid horen.

2. niet van iedere klantid een factuur is gemaakt en toch alle klanten
Dit komt doordat je een LEFT JOIN gebruikt. Je geeft daarmee aan dat je van de tabel die aan de linkerkant van de LEFT JOIN staat alle records wilt zien, onafhankelijk van het feit of er wel of geen bijbehoorde records in de rechter tabel zijn. Is er een passend record, dan wordt die informatie ingevuld. Is er geen passend record, dan worden de velden leeg gelaten. Als je i.p.v. de LEFT JOIN een INNER JOIN gebruikt, dan krijg je alleen de records die een match in beide tabellen heeft.