[SQL] Query met berekeningen in drie tabellen

[Verwijderd]

Ik ben al een tijdje bezig met een query om een bepaald totaalbedrag te berekenen aan de hand van verschillende waarden in twee tabellen in een database. Die berekeningen moeten realtime gemaakt worden omdat er veel gemuteerd wordt in de tabellen. Het lukt me echter niet om de juiste waardes terug te krijgen - ik krijg of waarden terug die een factor 2 te hoog zijn of helemaal niks.

Eerst de tabellen:

BESCHIKKINGEN	CONTRACTEN	CMFRECORD
bs_id	ct_id	id
bs_beschikkingsbedrag	ct_tarief	userkey
	ct_eenheden

Elk record in BESCHIKKINGEN of CONTRACTEN heeft altijd een corresponderend record in CMFRECORD. Daarbij zijn de id's van beide records gelijk *. De kolom userkey is nodig omdat ik alleen de rijen uit beide tabellen wil hebben van één bepaalde userkey.

(* Dit betekent dan ook dat de nummering van id's in de tabellen BESCHIKKINGEN en CONTRACTEN niet netjes doorlopen - die zullen bijvoorbeeld verspringen van 1, 3, 6, 7, 10 etc. De tussenliggende id's zijn dan in gebruik in andere tabellen.)

De waarden die ik terug wil hebben uit de query zijn:
- Credit: optelling van bs_beschikkingsbedrag van alle records van de betreffende userkey
- Debet: optelling van ct_tarief per rij vermenigvuldigd met ct_eenheden
- Saldo: credit min debet

Ik heb tot nu toe twee queries geprobeerd:

SQL:

SELECT SUM(b.BS_Beschikkingsbedrag) as Credit, 
(SUM(c.CT_Tarief)*SUM(c.CT_Eenheden)) as Debet, 
(SUM(b.BS_Beschikkingsbedrag)-(SUM(c.CT_Tarief)*SUM(c.CT_Eenheden))) AS Totaal 
FROM beschikkingen b, contracten c 
WHERE b.BS_ID IN (SELECT id FROM cmfrecord WHERE userkey = ?) 
AND c.CT_ID IN (SELECT id FROM cmfrecord WHERE userkey = ?)

Hierbij krijg ik nummerieke waardes terug die helemaal niet kloppen.

SQL:

SELECT SUM(beschikkingen.BS_Beschikkingsbedrag) as Credit, 
(SUM(contracten.CT_Tarief)*SUM(contracten.CT_Eenheden)) as Debet 
FROM beschikkingen INNER JOIN cmfrecord ON beschikkingen.BS_ID = cmfrecord.ID, 
contracten INNER JOIN cmfrecord ON contracten.CT_ID = cmfrecord.ID 
WHERE cmfrecord.userkey = ?

Hierbij krijg ik helemaal geen waardes terug (en dan zit de totaalberekening er nog niet eens in).

Het stomme is dat ik wél de goede credit en debetwaardes krijg als ik deze afzonderlijk query, het lukt echter niet in één query (en dat moet wel). Heeft iemand hier wellicht een idee over hoe dit te doen? Alvast veel dank!

(NB. opmerkingen over de databasestructuur hoeven niet, het was niet mijn idee en ik heb er geen invloed op )

[garciake]

Probeer eens het volgende

select sum(bs_beschikkingsbedrag) as credit,
sum(ct_tarief * ct_eenheden) as Debet,
sum(bs_beschikkingsbedrag) - sum(ct_tarief * ct_eenheden) as saldo
from cmfrecord
left outer join beschikkingen
on id = bs_od
left outer join contracten
on id = ct_od
where userkey = ?

[Verwijderd]

Shit, dat het zo simpel zou zijn Ik zat inderdaad vanaf de andere tabellen te redeneren, dit is veel makkelijker. Thanks voor het snelle antwoord!

Edit: hij werkt, voor de duidelijkheid

[ Voor 12% gewijzigd door Verwijderd op 17-07-2009 15:50 ]

[garciake]

geen probleem
moet niet altijd moeilijk he

[Creepy]

Even een tikje door naar PRG aangezien we het puur en alleen over SQL hebben...

[Verwijderd]

Ok, dank. Heb nog wel een vervolgvraag; ik wil nog een extra WHERE-clause toevoegen, die leidt alleen tot geen goed resultaat.

BESCHIKKINGEN	CONTRACTEN	CMFRECORD
bs_id	ct_id	id
bs_beschikkingsbedrag	ct_tarief	userkey
	ct_eenheden
	ct_gedeclareerd

Het veld ct_gedeclareerd is erbij gekomen en ik wil alleen de rijen meetellen waar 'ct_gedeclareerd' op '1' staat. Ik heb nu het volgende;

SQL:

SELECT SUM(BS_Beschikkingsbedrag) as Credit,
SUM(BS_Beschikkingsbedrag) - SUM(CT_Tarief * CT_Eenheden) as Saldo
FROM CMFRECORD
LEFT OUTER JOIN BESCHIKKINGEN
ON ID = BS_ID
LEFT OUTER JOIN CONTRACTEN
ON ID = CT_ID
WHERE USERKEY = ? AND CT_Gedeclareerd = '1'

Maar als ik die query uitvoer terwijl er geen enkele rij in CONTRACTEN geselecteerd wordt, krijg ik lege velden terug en als er wel een rij uit CONTRACTEN geselecteerd wordt, krijg ik alleen het veld 'Debet' terug, de overige twee niet Ik moet toch gewoon op deze manier criteria kunnen opgeven, net zoals met 'userkey'?

[ Voor 5% gewijzigd door Verwijderd op 20-07-2009 09:10 ]

[DamadmOO]

Met andere woorden: je wilt een selectievere join. Zet dan die laatste expressie eens in de join die je naar contracten doet en bekijk de resultaten

[Verwijderd]

Je bedoelt het zo?

SQL:

SELECT SUM(CT_Tarief * CT_Eenheden) as Debet,
SUM(BS_Beschikkingsbedrag) as Credit,
SUM(BS_Beschikkingsbedrag) - SUM(CT_Tarief * CT_Eenheden) as Saldo
FROM CMFRECORD
LEFT OUTER JOIN CONTRACTEN
ON ID = CT_ID
WHERE CT_Gedeclareerd = '1'
LEFT OUTER JOIN BESCHIKKINGEN
ON ID = BS_ID
WHERE USERKEY = ?

Dan krijg ik een foutmelding;

code:

Column not found: BS_BESCHIKKINGSBEDRAG in statement [SELECT SUM(CT_Tarief * CT_Eenheden) as Debet, SUM(BS_Beschikkingsbedrag) as Credit, SUM(BS_Beschikkingsbedrag) - SUM(CT_Tarief * CT_Eenheden) as Saldo FROM CMFRECORD LEFT OUTER JOIN CONTRACTEN ON ID = CT_ID WHERE CT_Gedeclareerd = '1' LEFT OUTER JOIN BESCHIKKINGEN ON ID = BS_ID WHERE USERKEY = ?]

[garciake]

verander de where naar AND
zoals hieronder:

SELECT SUM(CT_Tarief * CT_Eenheden) as Debet,
SUM(BS_Beschikkingsbedrag) as Credit,
SUM(BS_Beschikkingsbedrag) - SUM(CT_Tarief * CT_Eenheden) as Saldo
FROM CMFRECORD
LEFT OUTER JOIN CONTRACTEN
ON ID = CT_ID
AND CT_Gedeclareerd = '1'
LEFT OUTER JOIN BESCHIKKINGEN
ON ID = BS_ID
WHERE USERKEY = ?

[Verwijderd]

Werkt, super! Toch eens in die joins gaan verdiepen

[Verwijderd]

Toch snap ik iets niet. Wanneer ik een JOIN doe tussen tabellen met verschillend aantal rijen en dan per tabel een SUM() wil berekenen, gaat het ergens fout. Als ik in CONTRACTEN 2 rijen heb en in BESCHIKKINGEN maar 1, dan zit er in een SUM() die ik op BS_Beschikkingsbedrag doe een factor 2 afwijking Draai ik de query zonder de JOIN naar CONTRACTEN, dan krijg ik wél het goede resultaat. Hoe kan dat, als ik juist een LEFT of RIGHT JOIN doe?

Ik heb de tabellen iets aangepast waardoor ik de twee tabellen direct kan koppelen, op caseid. Ik heb dus meerdere rows in beide tabellen, maar die kunnen allemaal bij dezelfde caseid horen.

BESCHIKKINGEN	CONTRACTEN
bs_id	ct_id
bs_caseid	ct_caseid
bs_beschikkingsbedrag	ct_tarief
	ct_eenheden

Die vul ik dan als volgt.

BESCHIKKINGEN

bs_id	bs_caseid	bs_beschikkingsbedrag
1	1	2000

CONTRACTEN

ct_id	ct_caseid	ct_tarief	ct_eenheden
1	1	10	15
2	1	10	15

Dan doe ik bijvoorbeeld het volgende.

SQL:

SELECT b.bs_caseid, SUM(b.BS_Beschikkingsbedrag), SUM(c.CT_Tarief*c.CT_eenheden)
FROM contracten c
RIGHT JOIN beschikkingen b
ON b.bs_caseid = c.ct_caseid
GROUP BY b.bs_caseid

En dan krijg ik dit terug:

CASEID	SUM1	SUM2
1	4000	300

Heb ik drie rijen in CONTRACTEN, dan heeft SUM1 een afwijking van factor 3, terwijl SUM2 goed blijft

Wie kan mij aan het verstand krijgen waarom dat zo is?

[Niemand_Anders]

Als je even de group by weg laat zul je zien dat je twee regels hebt waar in de kolom bs_beschikkingsbedrag 2000 staat. Met de sum tel je deze dus bij elkaar op want ze hebben dezelfde caseid hebben.

Een gemene truck om deze toch gemakkelijk goed te krijgen is het gebruik van AVG waarmee je het gemiddelde terug krijgt.

SQL:

SELECT b.bs_caseid, AVR(b.BS_Beschikkingsbedrag), SUM(c.CT_Tarief*c.CT_eenheden) from ...

[Verwijderd]

Je bedoelt dat hij de twee rijen in CONTRACTEN allebei na elkaar aan de ene rij in BESCHIKKINGEN koppelt en dus twee keer die ene rij langsgaat? Hmm, klinkt wel aannemelijk. Maar ondervang ik dat niet door een andere JOIN te gebruiken?

Die truc met AVG() gaat me vooralsnog iets te ver, ik hoop dat ik dit nog op een nette manier kan oplossen

[silverstorm]

SUM levert product van het aantal regels met de formule tussen haakjes. Omdat er meerdere regels in contracten staan, is dit altijd diezelfde factor te hoog.
Wat jij bedoeld is waarschijnlijk.

SQL:

SELECT b.bs_caseid, b.BS_Beschikkingsbedrag, SUM(c.CT_Tarief*c.CT_eenheden)
FROM contracten c
RIGHT JOIN beschikkingen b
ON b.bs_caseid = c.ct_caseid
GROUP BY b.bs_caseid, b.BS_Beschikkingsbedrag

JSS de uitvoer van de query zonder de sum levert deze resultaten op:

caseid	bs_beschikkingsbedrag	c.CT_Tarief*c.CT_eenheden
1	2000	300
1	2000	300

Ga je de tweede kolom optellen komt daar inderdaad de 4000 uit. Als je daar de 2000 wil, moet je het niet someren, maar de waarde "los" uit de database gebruiken.

Niemand_Anders schreef op woensdag 22 juli 2009 @ 16:46:
Een gemene truck om deze toch gemakkelijk goed te krijgen is het gebruik van AVG waarmee je het gemiddelde terug krijgt.

Het gebruik van AVG is overbodig. Er is maar 1 veld die de waarde kan rekenen. Het gemiddelde over alleen x is altijd x.

[ Voor 23% gewijzigd door silverstorm op 23-07-2009 00:25 ]

[Verwijderd]

Dat is helder, maar wat nou als ik 2 rijen in BESCHIKKINGEN heb en 6 in CONTRACTEN? Dan wil ik die 2 rijen wel bij elkaar optellen

[silverstorm]

Heb je een voorbeeld met data? Ik begrijp je niet helemaal.

[plagvreugd]

De query van Silverstorm werkt volgens mij alleen als je ervan uitgaat dat case-id uniek is in de tabel beschikkingen. Dit is niet wat ik begrijp uit de tekst van TS: ik ga ervan uit dat case-id noch in beschikkingen; noch in contracten uniek is. Dan is denk de beste benadering om de som voor elke tabel afzonderlijk te berekenen per case-id en de boel daarna te joinen.

Zoiets als dit (niet getest, t gaat om t idee):
[code=sql]SELECT nvl(beschikkingcase, contractcase) as caseid, besch.credit, contr.debet
from
(SELECT b.bs_caseid as beschikkingcase, SUM(b.BS_Beschikkingsbedrag) as credit
FROM beschikkingen b
GROUP BY b.bs_caseid
) besch
FULL OUTER JOIN
(SELECT c.bs_caseid as contractcase , SUM(c.CT_Tarief*c.CT_eenheden) as debet
FROM contracten c
GROUP BY c.bs_caseid
) contr
ON besch.beschikkingcase = contr.contractcase[/code=sql]