SUM() in combi met LEFT JOIN

[XavierJeukens]

Hallo allen,

mijn vraag gaat over de volgende twee tabellen (vereenvoudigd):

PROJECTEN (id, budget);
KOSTENPOSTEN(id, project_id, bedrag);

Ik wil graag een tabel weergeven met daarin:
- Alle projecten met hun id en hun budget
- Het totaal aan gemaakte kosten per project

Indien er van een project GEEN kosten bestaan, dus geen enkele corresponderende records in de tabel "kostenposten", dan wil ik: 0.

Ik heb de volgende (syntactisch juiste) query geschreven:

SELECT p.id, p.budget, CASE sum( k.bedrag ) WHEN NULL THEN 0 ELSE sum(k.bedrag) AS kostenTotaal
FROM projecten p
LEFT JOIN kostenposten k ON k.project_id = p.id
GROUP BY p.id
LIMIT 0 , 30;


Wat er gebeurt is dat mijn waarden voor het afgeleide veld "kostenTotaal" altijd NULL blijven, en niet worden omgezet naar 0 als ik dat zou willen. Voer ik een CASE uit met een specifieke waarde, zoals CASE sum( k.bedrag ) WHEN 6100 THEN 'JA' ELSE 'NEE' dan worden alle NULLS omgezet naar 'NEE' en 6100 netjes naar 'JA'. M.a.w.: het CASE-statement laat zich alleen niet uitvoeren op NULLS, wel op specifieke waarden. Een foutmelding blijft echter altijd uit.

Strict gezien is de waarde ook niet NULL denk ik, maar gewoon: ontbrekend. Er bestaan geen kostenposten-records bij sommige projecten, i.p.v. dat er een veld is met de waarde NULL. Tóch werk ik met een gejoinde tabel waarin NULLS voorkomen, en daar zou je dus mee moeten kunnen werken.

Weet iemand hoe ik dit toch kan oplossen?

Wat ik al geprobeerd heb:
- Ik heb al geprobeerd om het CASE statement te gebruiken in combi met "+ 10 - 10" of "* 1", maar dat sorteert geen enkel effect (ook geen fout)
- Ik heb geprobeerd een CASE te maken waarin ik zeg: WHEN sum( k.bedrag ) > 0 THEN sum ( k.bedrag ) ELSE 0 maar dat leidde wel tot een foutmelding.

Ook is er niet te rekenen met het berekende totaal, een actie als "rest = budget - kostenTotaal" geeft dan eveneens in alle gevallen NULL terug.

Wie weet raad met dit probleem? Volgens mij is het iets heel kleins, maar ik kom er niet uit....

Met vriendelijke groet,
Xavier

 

[benne]

twee oplossingen

De eertse oplossing is eenvoudig maar eigenlijk niet zo mooi
Voor in je tabel Kostenposten voor ieder projekt een "0" regel in, zodat de functie sum wat te berekenen heeft, dus waarde 0

niet echt mooi ?
Het probleem wat je heb is al oud, het rekenen en/of vergelijking met een 0 waarde
Sql ken drie waardens 0, de waarde 0, de waarde NULL (geen waarde), en de waarde NULL ( niets, uitkomsten van berekeningen welke niet bestaan, bv. sum over NULL.
De verschillen in die twee laatste waardens is niet te zien.
Ik heb even moeten zoeken, probeer dit;

SELECT COALESCE (SUM(dbo.kostenposten.bedrag), 0) AS KostenTotaal, dbo.Projecten.butget, dbo.Projecten.id
FROM dbo.kostenposten RIGHT OUTER JOIN
dbo.Projecten ON dbo.kostenposten.projectenId = dbo.Projecten.id
GROUP BY dbo.Projecten.butget, dbo.Projecten.id


de functie COALESCE is bijna niet te vinden, maar uiteindelijk maakt deze functie van elke eerste NULL in de berekening een 0, dus eindresutaat is 0

sucses er mee, Ben van den Bogaard

 

[XavierJeukens]

Werkt prima!

Hartelijk dank Ben,

na enkele dagen afwezigheid heb ik je 2e oplossing gebruikt en het werkt perfect! Hartstikke bedankt!

Met vriendelijke groet,
Xavier

 

[XavierJeukens]

Toch nog een fout...

De methode blijkt toch nog niet volledig waterdicht. Ik zit nu met een geval waarin de berekende sum-waarde wordt verdubbeld. Dit gebeurt in een enkel geval, d.w.z. één project.

Welke kostenpost ik ook aan dit project hang, de sum ervan wordt verdubbeld. Bij alle andere projecten gebeurt dit niet. Heeft iemand enig idee hierover? Is er iets bekend over de functie COALESCE() of SUM() dat dit zich kan voordoen?

Ik puzzel zelf ook nog verder, mocht ik zelf toch nog een antwoord vinden dan post ik dat hier ook weer.

Mss ter verduidelijking nog even wat achtergrond-info:
Ik heb de volgende tabel kostenposten:

id, project_id, bedrag
1, 1, 10
2, 1, 20
3, 1, 30
4, 2, 10
5, 2, 20
6, 2, 30
7, 3, 10
8, 3, 20
9, 3, 30

En dan tel ik via een COALESCE(SUM(kostenposten.bedrag), 0) voor de projecten 1 t/m 10 de bedragen van de bijbehorende kostenposten op. Voor projecten zonder kostenposten moet ik dus 0 krijgen (i.p.v. NULL, daarover ging de oorspronkelijke vraag die werd opgelost), en voor de projecten 1 t/m 3 moet ik dus een som van 60 krijgen. Maar, bij project 3 krijg ik dus telkens 120. Hoe ik de bedragen ook aanpas, voor dat ene project komt er altijd dubbel zoveel uitrollen. Álle andere projecten gaan goed (in tegenstelling tot het voorbeeld heb ik er eigenlijk veel meer).

De volledige query die ik in het echte systeem gebruik is de volgende:
SELECT p.id, p.naam, p.budget, p.uren AS urenB, COALESCE( SUM( pmu.uren ) , 0 ) AS urenR, p.uurtarief, COALESCE( SUM( pmu.uren ) , 0 ) * p.uurtarief AS Loon, p.budget - p.OOP_begroot AS loonMax, (p.budget - p.OOP_begroot) - ( COALESCE( SUM( pmu.uren ) , 0 ) * p.uurtarief ) AS loonOver, p.OOP_begroot AS OOP_B, COALESCE(SUM(k.bedrag), 0) AS OOP, p.OOP_begroot - COALESCE( SUM( k.bedrag ) , 0 ) AS OOP_R, (p.budget - ( COALESCE( SUM( pmu.uren ) , 0 ) * p.uurtarief ) - ( COALESCE( SUM( k.bedrag ) , 0 ) )) AS Rest FROM projecten p LEFT JOIN kostenposten k ON k.project_id = p.id LEFT JOIN projecturenmedewerkers pmu ON pmu.project_id = p.id GROUP BY p.id

Wie weet een antwoord, of heeft een idee over de richting waarin ik het probleem moet zoeken?

 

[benne]

index

Het lijkt erop dat er wat aan de hand is met de index van beide tabellen.
Je geef aan hoe de tabellen en querys er uit zien, maar je heb het nog niet over de indexering gehad, misschien heb je dat wel in orde, dan zoeken we verder.

Groetjes, Ben

 

[XavierJeukens]

Indices

Hey Ben,

ik hoop dat je gelijk hebt. De indices van alle betrokken tabellen uit die volledige query zijn:

projecten p: int(4), NIET NULL, auto_increment
kostenposten k: int(4), NIET NULL, auto_increment
projecturenmedewerkers pmu: NIET NULL

De sleutel van pmu bestaat uit de unieke combinatie van een project_id en een medewerker_id. De types van de velden komen overeen met de oorspronkelijke tabellen.

 

[XavierJeukens]

Projecturenmedewerkers

Hallo Ben,

ik zit het probleem op het spoor. De juiste sum-waarde wordt vermenigvuldigd met het aantal medewerkers waarvan uren is opgegeven. Zeg ik dus dat er twee medewerkers aan een project hebben gewerkt, dan wordt vreemd genoeg het totaal aan gemaakte kostenposten (wat daar verder niks mee te maken heeft) verdubbeld. Bij drie medewerkers gaat het totaal aan kostenposten maal drie. Héél vreemd... maar je zat wel warm dus. De JOIN gata dus ergens verkeerd, lijkt me.

Nu nog een oplossing.... .

 

[XavierJeukens]

Mogelijke oplossing

Hai Ben,

volgens mij ligt de oplossing erin om de verschillende rijen met gemaakte uren door medewerkers per project (in de tabel projecturenmedewerkers) te groeperen per project id, VOOR het uitvoeren van de join met de andere tabellen. Dan zou je dus moeten gaan werken met derived tables en aliases, ik weet alleen niet precies hoe je dat moet doen in SQL. Maar ik denk wel dat het zo zou zijn op te lossen, wat jij?

 

[benne]

Welke sql versie werk je, de manier welke ik zie zoals je de sql gebruikt lijkt het op een oudere versie van vms digital maar dat zal wel niet
Ik werk met sql 2000 sp3 van microsoft.
kan je gedetaileerder de twee tabellen presenteren, dan kan ik hier wat proberen, eventueel kan je ze via e-mail sturen.

Ben