[MYSQL] Query retourneert maar 10 maanden

[wautah91]

ik ben bezig met een query op mijn mysql database op de tabel met de naam inkoop. Naar aanleiding van voorbeelden heb ik ook een kalender tabel aangemaakt met daarin dagen, maanden en jaren.

ik wil een overzicht van de SOM van de inkoop per maand, maar er is niet iedere maand iets ingekocht. Ik wil dat de query dan 0 retourneert. Met onderstaande code krijg ik toch maar 10 maanden terug.

MySQL:

SET lc_time_names = 'nl_nl';

SELECT
kalender.maand,
COALESCE(inkoop.BedragInclBTW,0) AS BedragInclBTW

FROM kalender


LEFT JOIN inkoop ON kalender.maand = MONTH(inkoop.Factuurdatum)

WHERE year(inkoop.Factuurdatum) = 2023

GROUP BY year(2023),month(Factuurdatum)
ORDER BY year(2023),month(Factuurdatum)

Volgens mij gaat het mis in de join van MONTH(inkoop.Factuurdatum) maar de voorbeelden op internet zeggen dat het zo zou moeten werken.

Wie kan mij een duwtje in de richting geven?

[Thijs1987]

Hey,

Doordat je de WHERE statement hebt geschreven op inkoop, maak je in essentie van de LEFT JOIN een INNER JOIN. Er van uitgaande dat de maanden zonder inkoop geen Factuurdatum veld hebben, filter je deze er uit.

Je zou kunnen proberen om er een CROSS JOIN van te maken en de datum te filteren op de kalender tabel. Of de WHERE statement veranderen in WHERE (year(xx) = 2023 OR year(xx) IS NULL), al denk ik dat de eerste oplossing beter werkt.

Laat maar weten of dit werkt.

Edit: review ook even je GROUP en ORDER statement. Hier kan je 'enkel' kalender.maand neerzetten.

[ Voor 9% gewijzigd door Thijs1987 op 29-02-2024 11:16 ]

[Voutloos]

De grootste fout is dat je geen aggregate functions gebruikt in je select. Daar mist sowieso een SUM(). Nu krijg je random data, omdat mysql als enige db daar niet altijd een error op gooit.

Als je maanden zonder inkopen ook wil, heeft Thijs gelijk over de where clause, maar is de eenvoudigste fix dat je juist filtert op kalender jaar.

[wautah91]

@Thijs1987 en @Voutloos bedankt voor jullie antwoorden.

Ik heb de query als volgt aangepast:

MySQL:

SET lc_time_names = 'nl_nl';

SELECT
kalender.maand,
COALESCE(inkoop.BedragInclBTW,0) AS BedragInclBTW

FROM kalender


LEFT JOIN inkoop ON kalender.maand = MONTH(inkoop.Factuurdatum)

WHERE year(kalender.db_date) = {{Jaarselector.selectedOptionValue}}

GROUP BY year({{Jaarselector.selectedOptionValue}}),month(Factuurdatum)
ORDER BY year({{Jaarselector.selectedOptionValue}}),month(Factuurdatum)

ik krijg nu 12 maanden terug én ook getallen die ik wel geloof.

wanneer ik COALESCE(SUM(inkoop.BedragInclBTW),0) AS BedragInclBTW doe, krijg ik zeer grote getallen. Dus dat werkt niet zoals verwacht.

[Voutloos]

Elke uiteindelijke query hier waarbij je niet met een Sum() selecteert is fout. Full stop.

Dus waarom is het bedrag dan raar? Kijk eens eerst naar de data zonder sums en group by’s. Hint/gok: je telt iets dubbel en dat zie je dan meteen.

[wautah91]

Voutloos schreef op donderdag 29 februari 2024 @ 11:46:
Elke uiteindelijke query hier waarbij je niet met een Sum() selecteert is fout. Full stop.

Dus waarom is het bedrag dan raar? Kijk eens eerst naar de data zonder sums en group by’s. Hint/gok: je telt iets dubbel en dat zie je dan meteen.

ja dat gebeurd zeker.

wanneer ik

MySQL:

SET lc_time_names = 'nl_nl';

SELECT
kalender.maand,
inkoop.BedragInclBTW AS BedragInclBTW

FROM kalender


LEFT JOIN inkoop ON kalender.maand = MONTH(inkoop.Factuurdatum)

WHERE year(kalender.db_date) = {{Jaarselector.selectedOptionValue}}

krijg ik voor januari 31 regels terug met hetzelfde bedrag, voor februari 28 regels met een ander bedrag etc.

maar wat ik eigenlijk nog vreemder vindt is dat er voor 2024 nog geen inkoop is, en ik al wel een resultaat daarvoor krijg.

MySQL:

SET lc_time_names = 'nl_nl';

SELECT
kalender.maand,
COALESCE(SUM(inkoop.BedragInclBTW),0) AS BedragInclBTW

FROM kalender


LEFT JOIN inkoop ON kalender.maand = MONTH(inkoop.Factuurdatum)

WHERE year(kalender.db_date) = {{Jaarselector.selectedOptionValue}}

GROUP BY kalender.maand
ORDER BY kalender.maand

[Knutselsmurf]

Je kalender-tabel bevat alle dagen. Als je dus je inkoop-tabel joint met je kalender-tabel op basis van het feit dat de maand overeen moet komen, zullen er 31 datums zijn die matchen met een inkoop-datum van bijvoorbeeld 15-01-2024. Als je dan een SUM() en group by maand doet, wordt iedere regel 31 maal meegenomen, omdat deze matched met iedere individuele datum in de betreffende maand.

Je moet dus zorgen dat iedere inkoop-record matched met precies 1 kalender-record. Dan kun je wel een SUM() uitvoeren zonder dubbeltellingen.

[wautah91]

Ja volgens mij ben ik er

MySQL:

SET lc_time_names = 'nl_nl';

SELECT


kalender.maand,
COALESCE(SUM(inkoop.BedragInclBTW),0) as BedragInclBTW


FROM kalender

left join inkoop on inkoop.Factuurdatum = kalender.db_date  

WHERE year(kalender.db_date) = "2023"

GROUP BY MONTH(kalender.db_date)
ORDER BY MONTH(kalender.db_date)

de left join ging mis omdat factuurdatum inderdaad een dag is die ik match op een maand. Nu match ik dmv db_date ook op een dag. Met COALESCE voer ik een 0 in als er geen waarde is.

Bedankt voor jullie hulp!

[Voutloos]

Yes, dit was precies de oorzaak die ik verwachtte, dus weer iemand leren vissen.

Voor de puntjes op de i: Eigenlijk is de kalender.maand die je nu selecteert niet deterministisch; het staat niet in group by of is geen aggregate function. Dus je krijgt 1 van de 28-31 waardes at random. Dus nogmaals, dat mag enkel bij mysql.
In dit geval is het redelijk voor jou te redeneren dat de random waarde in principe alsnog juist is, maar je query is pas echt correct als je ook in de select MONTH(kalender.db_date) gebruikt.

[Knutselsmurf]

Voutloos schreef op donderdag 29 februari 2024 @ 15:32:
Yes, dit was precies de oorzaak die ik verwachtte, dus weer iemand leren vissen.

Voor de puntjes op de i: Eigenlijk is de kalender.maand die je nu selecteert niet deterministisch; het staat niet in group by of is geen aggregate function. Dus je krijgt 1 van de 28-31 waardes at random. Dus nogmaals, dat mag enkel bij mysql.
In dit geval is het redelijk voor jou te redeneren dat de random waarde in principe alsnog juist is, maar je query is pas echt correct als je ook in de select MONTH(kalender.db_date) gebruikt.

Nog beter is om juist niet MONTH(kalender.db_date) te gebruiken, maar het veld kalender.maand, waar, als het goed is, een index op zit.

Hetzelfde voor de selectie van het jaar. Nu wordt er geselecteerd op year(db_date), maar beter is een selectie op kalender.jaar, wat een veld is met een index.

Op het moment dat er een functie wordt gebruik in de selectie, zal er een full-table-scan uitgevoerd worden, wat een langzamere query tot gevolg heeft.

Als voorbeeld: Ik heb in een aantal applicaties ook een vaste datum-tabel, met daarin de volgende velden:

- datum (PK)
- jaar
- maand
- dag
- dag v/d week
- weeknummer
- weeknummerjaar

en indexen op (jaar,maand) en (weeknummerjaar,weeknummer,dag v/d week)

[Voutloos]

Er is vast van alles te tunen, maar het eerste uitgangspunt is een correcte, deterministische query.

[wautah91]

Bedankt voor de aanvullende tips!

Toch gaat er nog iets mis..

Een andere tabel met dezelfde query

MySQL:

SET lc_time_names = 'nl_nl';

SELECT


kalender.maand,
COALESCE(SUM(afspraken.betaald),0) as Betaald


FROM kalender

left join afspraken on afspraken.datum = kalender.db_date  

WHERE year(kalender.db_date) = {{Jaarselector.selectedOptionValue}}

GROUP BY MONTH(kalender.db_date)
ORDER BY MONTH(kalender.db_date)

Ik wil uit de tabel afspraken de kolom betaald opsommen per maand.

Wanneer ik bovenstaande uitvoer voor 2024, krijg ik van januari 0 en februari 26.71 wat beide niet klopt als ik de ruwe tabel exporteer naar excel en daar de som doe van die maanden.


Ik zit nu 2023 nog even te controleren maar daar klopt ook niks van. De maanden januari tot juni hebben in de query en excel hetzelfde resultaat, de maanden juli t/m wijken af..

[ Voor 11% gewijzigd door wautah91 op 03-03-2024 13:02 ]

[Voutloos]

Je groepeert per maand, en je controleert per maand én jaar. Rara wat gaat er mis?

Ik zei het al eerder, maak anders de query eerst zonder groeperen, of zorg dat ie die variant ook hebt. Dus dat je enkel de specifieke data ziet welke je wil optellen.

[ Voor 49% gewijzigd door Voutloos op 03-03-2024 13:33 ]

[wautah91]

Voutloos schreef op zondag 3 maart 2024 @ 13:30:
Je groepeert per maand, en je controleert per maand én jaar. Rara wat gaat er mis?

Ik zei het al eerder, maak anders de query eerst zonder groeperen, of zorg dat ie die variant ook hebt. Dus dat je enkel de specifieke data ziet welke je wil optellen.

Ik ben er achter volgens mij. De kalender tabel bevat datums zonder tijd. Vanaf juli komen er ook datums met tijden in de tabel afspraken voor. Als ik geen groups toepas geeft de kolom kalender.maand de waarde NULL terug. Daarom gaat het in het jaar 2024 ook mis.

Hoe kan ik joinen op afspraken.datum zonder time?

[RobIII]

Doe eens gek en pak de documentatie er eens bij en neus eens tussen de datetime functions. Als je daar bij Date() kijkt lees je:

quote: https://dev.mysql.com/doc...ctions.html#function_date

Extracts the date part of the date or datetime expression expr. Returns NULL if expr is NULL.

SQL:

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

Dus... probeer dat eens?

SQL:

...
left join afspraken on date(afspraken.datum) = kalender.db_date  
...

[ Voor 7% gewijzigd door RobIII op 03-03-2024 19:58 ]

[wautah91]

RobIII schreef op zondag 3 maart 2024 @ 19:57:
Doe eens gek en pak de documentatie er eens bij en neus eens tussen de datetime functions. Als je daar bij Date() kijkt lees je:

[...]

Dus... probeer dat eens?

SQL:

...
left join afspraken on date(afspraken.datum) = kalender.db_date  
...

Yes dit werkt, bedankt!