Al een tijdje ben ik bezig om een uitgebreide MySQL query te maken. Ik weet alleen niet hoe ik het goed krijg en ik hoop dat iemand mij ermee kan helpen. Ik heb 2 tabellen in de database staan
Tabel: "bon"
Hierin staan de bonnen van verkochte producten. Status 1 betekend dat de bon betaald is.
id datum prijs status 1 27-04-2017 100.00 1 2 28-04-2017 95.86 0 3 28-04-2017 197.45 0 4 29-04-2017 36.98 1
Tabel: "retour"
Hierin staan producten die retour gebracht zijn.
id datum prijs 1 24-04-2017 20.00 2 24-04-2017 37.00 3 27-04-2017 198.00 4 29-04-2017 129.36
Wat ik uiteindelijk als output wil hebben:
Per dag wil ik zien wat er open staat, wat er betaald is, wat retour is gebracht en uiteindelijk de winst (open + betaald - retour bedrag). Als er op een dag geen bonnen of retour zijn wil ik wel dat deze dag in de tabel komt te staan met 0 erin.
Datum Open Betaald Retour Winst 24-04-2017 100.00 534.89 20.49 614.40 25-04-2017 238.95 429.95 30.95 637.95 26-04-2017 95.45 756.90 175.99 676.36 27-04-2017 0 0 0 0 28-04-2017 28.45 295.95 0 324.40
De gebruik kan zelf al een maand + jaar kiezen vanuit een dropdown. Deze gegevens heb ik in de variable $month & $year staan.
Wat ik nu heb is onderstaande query maar hiermee doe ik niks met de retour gebrachte artikelen en zit er maar 1 status in de query. Op dit moment heb ik 2 aparte tabellen, openstaande bonnen en betaalde bonnen maar dit wil ik eigenlijk in 1 tabel krijgen.
SELECT DAY(datum), SUM(`prijs`) as prijs_totaal, datum FROM `bon` WHERE YEAR(datum) = $year AND MONTH(datum) = $month AND status=1 GROUP BY YEAR(datum), MONTH(datum), DAY(datum)
Ik hoop dat iemand mij kan helpen met de query waarmee ik deze gegevens mooi in 1 tabel kan krijgen.
- Uitgebreide MySQL query
-
29-04-2017, 11:19 #1
- Berichten
- 1.407
- Lid sinds
- 16 Jaar
Uitgebreide MySQL query
-
In de schijnwerper
-
29-04-2017, 12:16 #2
- Berichten
- 1.915
- Lid sinds
- 18 Jaar
Re: Uitgebreide MySQL query
Waarom wil je dit perse vanuit een query doen? Dit zou veel efficiënter, leesbaarder én onderhoudbaarder zijn als je dit vanuit de code opbouwt.
-
29-04-2017, 12:59 #364BitsWebhosting.EU
- Berichten
- 2.092
- Lid sinds
- 17 Jaar
Re: Uitgebreide MySQL query
Als eerste kun je voor die datum al een BETWEEN gebruiken en de query een stuk sneller te maken door niet 2 functies voor ELK record in de db uit te voeren.
Dan kun je met de mysql constructie: "if(status = 1, prijs, 0) as verkocht, if(status = 0, prijs, 0) as open" al de verkocht/open velden selecteren per dag. Dit zijn in principe ook 2 functies (veel sneller dan een YEAR() of MONTH()) en werken alleen op de resultset die aan de where voldoet. Veel minder vaak dus.
In principe kun je met een subselect ook nog het retour bedrag erbij halen, maar dat betekent een per definitie niet te cachen query voor het aantal dagen dat er bonnen zijn. Dus dat is duf.
Dan kun je beter een enkele select doen om de retour bedragen van die maand op te halen. Dit zijn max 31 rows in 1 keer die perfect gebruik kunnen maken van de index op datum.
De tabel opbouwen doe je inderdaad via php omdat je daar in een loopje beiden resultsets uitleest en de dagen die in beide tabellen niet voorkomen 'static' in je tabel toevoegt.
-
29-04-2017, 13:00 #4
- Berichten
- 1.915
- Lid sinds
- 18 Jaar
Re: Uitgebreide MySQL query
Even vlug wat gemaakt om mijn MySQL skills te testen. Dit geeft alleen de dagen terug waar resultaat op is geweest. Ik kan eventueel ook de dagen terug laten komen door een temptable bij te zetten, met alle waardes op 0, maar dat vond ik voor nu even te veel werk. Ik denk dat je wel snap waarom ik het advies gaf om dit in code te doen. :)
PHP Code:SELECT
d.Datum,
SUM(d.Open) As Open
, SUM(d.Betaald) As Betaald
, SUM(d.Retour) As Retour
FROM ((SELECT retour.datum as Datum
, 0 As Open
, 0 As Betaald
, (
SELECT IFNULL(SUM(retour2.prijs),0)
FROM retour as retour2
WHERE retour2.datum = retour.datum
) As Retour
FROM retour)
UNION ALL
(SELECT bon.datum as Datum
,(
SELECT IFNULL(SUM(bon2.prijs),0)
FROM bon AS bon2
WHERE bon2.datum = bon.datum
AND status = 0
) AS Open
,(
SELECT IFNULL(SUM(bon2.prijs),0)
FROM bon AS bon2
WHERE bon2.datum = bon.datum
AND status = 1
) AS Betaald
,0 as Retour
FROM bon)) as d
GROUP BY Datum
-
01-05-2017, 08:59 #5
- Berichten
- 1.407
- Lid sinds
- 16 Jaar
Re: Uitgebreide MySQL query
Hartstikke bedankt voor de reacties en tips! Danny ik ga zo je code proberen, ik laat het je weten of het hiermee gelukt is.
-
02-05-2017, 14:47 #6
- Berichten
- 1.915
- Lid sinds
- 18 Jaar
Re: Uitgebreide MySQL query
Ik raad eigenlijk niet aan om deze te gebruiken. Het was voornamelijk om te laten zien hoe complex een dergelijke query is. Dit soort queries hebben heel snel de neiging om traag te worden. Voer 2 simpele queries uit en bouw het gewenste resultaat op vanuit de code. Dat is de beste manier om hier mee om te gaan.
Plaats een
- + Advertentie
- + Onderwerp
Marktplaats
Webmasterforum
- Websites algemeen
- Sitechecks
- Marketing
- Domeinen algemeen
- Waardebepaling
- CMS
- Wordpress
- Joomla
- Magento
- Google algemeen
- SEO
- Analytics
- Adsense
- Adwords
- HTML / XHTML
- CSS
- Programmeren
- PHP
- Javascript
- JQuery
- MySQL
- Ondernemen algemeen
- Belastingen
- Juridisch
- Grafisch ontwerp
- Hosting Algemeen
- Hardware Info
- Offtopic