SUM van DATEDIFF met UNIQUE id-nummer
Nu wil ik met één Query de totale omzet van alle facturen ophalen. Hiervoor ben ik echt een monsterquery aan het schrijven, Naast de omzet wil ik ook graag het totaal aantal afgenomen nachten weergeven. Dit zou uit dezelfde query te halen moeten zijn.
Situatie:
Ik heb 3 tabellen:
Tabel 1 is facturen, hieronder hangen 1 of meerdere reserveringen en onder iedere reserveringe kunnen weer meerdere opties hangen (bijv. ontbijt). In alle drie de tabellen zijn er prijzen vastgelegd. In tabel 1 de eventuele prijs voor de betaalmethode en eventuele factuurkorting, in tabel 2 de kamerprijs en in tabel 3 de prijs van de opties.
Code (php)
1
2
3
4
5
2
3
4
5
TABEL1: facturen
T1_ID | T1_PRICE
-----------------
1 | 5.00
T1_ID | T1_PRICE
-----------------
1 | 5.00
Code (php)
1
2
3
4
5
6
2
3
4
5
6
TABEL2: reserveringen
T1_ID | T2_ID | T2_DATE_START | T2_DATE_END | T2_PRICE
------------------------------------------------------
1 | 1 | 01-01-2013 | 03-01-2013 | 100.00
1 | 2 | 01-02-2013 | 02-02-2013 | 150.00
T1_ID | T2_ID | T2_DATE_START | T2_DATE_END | T2_PRICE
------------------------------------------------------
1 | 1 | 01-01-2013 | 03-01-2013 | 100.00
1 | 2 | 01-02-2013 | 02-02-2013 | 150.00
Code (php)
1
2
3
4
5
6
2
3
4
5
6
TABEL3: reserverings opties
T1.ID | T2_ID | T3_ID | T3_PRICE
--------------------------------
1 | 2 | 1 | 15.00
1 | 2 | 2 | 10.00
T1.ID | T2_ID | T3_ID | T3_PRICE
--------------------------------
1 | 2 | 1 | 15.00
1 | 2 | 2 | 10.00
Query:
Ik heb om de omzet op te tellen een SELECT [velden die ik nodig heb] FROM TABEL3 gemaakt, met een LEFT JOIN van TABEL2 en TABEL3. Nu wil ik de totale omzet berekenen en ik zou ook het aantal nachten willen weergeven.
Met behulp van DATEDIFF kan ik het aantal nachten uitrekenen.
MET SUM kan ik de waarde op laten tellen zodat alles uiteindelijk in één veld komt te staan.
Het probleem is nu echter dat door de JOINS dit ik gedaan heb een factuur meerdere keren voor gaat komen wanneer er meerdere reserveringen aan hangen en ook dat een reservering meerdere keren gaat voorkomen wanneer er meerdere opties aan hangen.
De SELECT zoals hierboven omschreven levert deze tabel op. Onderaan zet ik de totalen zoals deze nu m.b.v. SUM berekent zouden worden:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
T1_ID | T1_PRICE | T1_ID | T2_ID | T2_DATE_START | T2_DATE_END | T2_DATEDIFF | T2_PRICE | T1.ID | T2_ID | T3_ID | T3_PRICE
---------------------------------------------------------------------------------------------------------------------------
1 | 5.00 | 1 | 1 | 01-01-2013 | 03-01-2013 | 2 | 100.00 | NULL | NULL | NULL | NULL
1 | 5.00 | 1 | 2 | 01-02-2013 | 02-02-2013 | 1 | 150.00 | 1 | 2 | 1 | 15.00
1 | 5.00 | 1 | 2 | 01-02-2013 | 02-02-2013 | 1 | 150.00 | 1 | 2 | 2 | 10.00
---------------------------------------------------------------------------------------------------------------------------
| 15.00 | | | | | 4 | 400.00 | | | | 25.00
---------------------------------------------------------------------------------------------------------------------------
1 | 5.00 | 1 | 1 | 01-01-2013 | 03-01-2013 | 2 | 100.00 | NULL | NULL | NULL | NULL
1 | 5.00 | 1 | 2 | 01-02-2013 | 02-02-2013 | 1 | 150.00 | 1 | 2 | 1 | 15.00
1 | 5.00 | 1 | 2 | 01-02-2013 | 02-02-2013 | 1 | 150.00 | 1 | 2 | 2 | 10.00
---------------------------------------------------------------------------------------------------------------------------
| 15.00 | | | | | 4 | 400.00 | | | | 25.00
Van deze totalen is nu echter alleen de SUM(T3_PRICE) correct. De waarden zouden namelijk als volgt moeten zijn:
Code (php)
1
| 5.00 | | | | | 3 | 250.00 | | | | 25.00
Op een of andere manier moet ik dus aan MYSQL doorgeven dat de T1_PRICE maar één keer per DISTINCT(T1_ID) en de T2_DATEDIFF en T2_PRICE maar één keer per DISTINCT(combinatie van T1_ID & T2_ID) opgeteld zou moeten worden.
Wie kan mij op weg helpen. Ook als dit volgens jullie gewoon niet mogelijk is hoor ik het graag. Dan ga ik het namelijk binnen PHP met loops oplossen, maar ik wil het (als het kan) het lieft met één query oplossen.
Gewijzigd op 12/11/2013 23:09:39 door Lars Anderson
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT
SUM(fr.factuurprijs) omzet,
SUM(fr.nights) aantal_nachten
FROM
(SELECT
t1_id,
f.t1_price + SUM(ro.prijs_reservering) factuurprijs,
SUM(ro.nachten) nights
FROM
facturen f
JOIN
(SELECT
r.t1_id,
t2_id,
r.t2_price + COALESCE(SUM(o.t3_price),0) prijs_reservering,
DATEDIFF(r.t2_end, r.t2_start) nachten
FROM
reserveringen r
LEFT JOIN
opties o
USING (t2_id)
GROUP BY
r.t1_id,t2_id, nachten
) ro
USING (t1_id)
GROUP BY
t1_id
) fr
SUM(fr.factuurprijs) omzet,
SUM(fr.nights) aantal_nachten
FROM
(SELECT
t1_id,
f.t1_price + SUM(ro.prijs_reservering) factuurprijs,
SUM(ro.nachten) nights
FROM
facturen f
JOIN
(SELECT
r.t1_id,
t2_id,
r.t2_price + COALESCE(SUM(o.t3_price),0) prijs_reservering,
DATEDIFF(r.t2_end, r.t2_start) nachten
FROM
reserveringen r
LEFT JOIN
opties o
USING (t2_id)
GROUP BY
r.t1_id,t2_id, nachten
) ro
USING (t1_id)
GROUP BY
t1_id
) fr
Overigens is factuur id in de opties tabel overbodig
Gewijzigd op 13/11/2013 09:07:19 door Ger van Steenderen
De invoice_id is trouwens niet overbodig, omdat de 'reservation_id' niet uniek is. Die begint per 'invoice_id' op 1.
Toevoeging op 13/11/2013 14:35:20:
Beste Ger,
Heel erg bedankt weer. Ik heb aan de praat gekregen zoals ik wilde met behulp van jouw voorbeeld query. Super bedankt!!!