[SQL] Access: sum van bepaalde velden

[VonAalscht]

* sorry voor topictitel, weet niet goed wat het probleem in 1 zin samenvat...

Ik heb een tabel met twee velden, volgNum en hoeveelheid; volgNum is een serienummer, opgebouwd uit 12 cijfers. De eerste 10 cijfers zijn uniek, geven een transactie aan. De laatste 2 cijfers geven een volgnummer van de transactie aan. Het kan dus zijn dat het volgnummer tot 06 oploopt (of iets dergelijks); het gaat er om dat ik dat niet vantevoren kan weten.

code:

volgNum                Hoeveelheid
--------------------------------------------------
243610380101   |    62,5200004577637
243610380201   |    33,7999992370605
243610380202   |    10,2399997711182
243610380301   |    32,8899993896484
243610380401   |    31,3099994659424
--------------------------------------------------

Ik ben naar iets op zoek waardoor ik de waarden van transacties met hetzelfde nummer (243610380201 en 243610380202) op kan tellen., oftewel, dat ik op volgnummer kan gaan selecteren bij optellen.

Het volgende heb ik geproduceerd, maar dan krijg ik gewoon de originele waarden terug als output

code:

SELECT  volgNum, SUM(totaal_hoeveelheid)
FROM volgNumHoeveelheidTabel
WHERE volgNum LIKE left(volgNum,10)&'**'
GROUP BY volgNum, totaal_hoeveelheid

Iemand een ideetje ?

[gorgi_19]

SQL:

SELECT  volgNum, SUM(totaal_hoeveelheid)
FROM volgNumHoeveelheidTabel
WHERE volgNum LIKE left(volgNum,10)&'**'
GROUP BY volgNum

?

edit:

Niet goed gelezen dus... groeperen op eerste 10 cijfers

[ Voor 38% gewijzigd door gorgi_19 op 19-12-2003 12:47 ]

[VonAalscht]

Ik dacht (*dacht* ) met die where clausule wat te kunnen selecteren op de eerste 10 cijfers van het volgnummer

[gorgi_19]

Ik heb totaal_hoeveelheid uit je group by clausule gesloopt, omdat die er helemaal niet in thuishoort, afaik. Je wilt namelijk niet groeperen op dit veld.

edit:

Ah, nu snap ik het...

[ Voor 37% gewijzigd door gorgi_19 op 19-12-2003 12:46 ]

[bigbeng]

De kriebels lopen over mijn rug en ik bedoel niet die goeie. Heb je wel eens gehoord van normaliseren? Nou ja, degene die de tabel heeft ontworpen dan, dat hoef jij natuurlijk niet te zijn.

In ieder geval is de group by de oorzaak van het leed. Ik weet niet wat voor database het is waar je op werkt, maar je zou kunnen proberen een group by op left(volgnum,10) bladibla te doen, misschien dat ie dat vreet. En group by op totaal_hoeveelheid moet je zeker niet doen...

[VonAalscht]

ja inderdaad, die stond er per ongeluk nog in, maar dat doet niets af aan de output die ik krijg

[jwpmzijl]

Dit geeft een kolom met alleen de transactienummers. Op deze kolom wordt geselecteerd. Vervolgens voeg je de kolom toe die je wilt optellen.

code:

SELECT Left([faktuurnummer],6) AS Expr1
FROM Fakturen
GROUP BY Left([faktuurnummer],6);

Dit probleem heb je niet als jij een beter datamodel hanteert (normalisatie zoals eerder gezegd). Ik ga er even vanuit dat je geen invloed hebt op dat datamodel maar dat je de data vanuit andere bron krijgt.

[ Voor 30% gewijzigd door jwpmzijl op 19-12-2003 12:47 ]

[VonAalscht]

bigbeng schreef op 19 december 2003 @ 12:45:
maar je zou kunnen proberen een group by op left(volgnum,10) bladibla te doen, misschien dat ie dat vreet. En group by op totaal_hoeveelheid moet je zeker niet doen...

Heb ik al geprobeerd, maar dan moet ik (van Access) in de select precies hetzelfde doen, anders pakt ie 'm niet, maar dan krijg ik nog steeds dezelfde output, alleen met 10 cijfers in plaats van 12

[P_de_B]

Kun je niet de tabel uitbreiden met twee extra velden?

LinkerGedeelteVanHetNummer
RechterTweeCijfersVanHetNummer

Deze hoef je slechts eenmaal de vullen(bij het toevoegen van een record) en kun je daarna in queries als bovenstaande gebruiken. Voordeel is dan ook dat je indexen kunt gebruiken, dat lukt niet als je een LEFT() of iets dergelijks gebruikt.

[VonAalscht]

P_de_B schreef op 19 december 2003 @ 12:49:
Kun je niet de tabel uitbreiden met twee extra velden?

LinkerGedeelteVanHetNummer
RechterTweeCijfersVanHetNummer

Deze hoef je slechts eenmaal de vullen(bij het toevoegen van een record) en kun je daarna in queries als bovenstaande gebruiken. Voordeel is dan ook dat je indexen kunt gebruiken, dat lukt niet als je een LEFT() of iets dergelijks gebruikt.

Hoe wou je dit verder aan gaan pakken ? dan krijg ik nog steeds dubbele transactie nummers terug..

De opmerkingen over beter datamodel zijn terecht en heb ik zelf al eerder bij mijn baas geplaatst, maar ik zit nu eenmaal aan deze opzet vast

[ Voor 12% gewijzigd door VonAalscht op 19-12-2003 12:53 ]

[P_de_B]

Huurmoordenaars schreef op 19 december 2003 @ 12:52:
[...]


Hoe wou je dit verder aan gaan pakken ? dan krijg ik nog steeds dubbele transactie nummers terug..

Nee hoor, je kunt dan een group by op LinkerGedeelteVanHetNummer (2436103802) doen en niet op HeleNummerDatEenBeetjeRaarIs ( 243610380201), dat was toch de bedoeling?

[bigbeng]

Ik heb het volgende ff getest:

code:

SELECT Left(volgNum,10) AS Expr1, Sum(volgNumHoeveelheid.hoeveelheid) AS SomVanhoeveelheid
FROM volgNumHoeveelheid
WHERE (((volgNumHoeveelheid.volgnum) Like Left([volgNum],10) & '**'))
GROUP BY Left(volgNum,10);

en dat gaf als resultaat:

code:

Expr1   SomVanhoeveelheid
0123456789  35,695424
0123456798  246,23

gebruikte gegevens:

code:

volgnum hoeveelheid
012345678901    12,238724
012345678902    23,4567
012345679801    123
012345679802    123,23

Dit is toch het antwoord op je vraag?

[VonAalscht]

bigbeng, precies wat ik zocht, bedankt ! (iedereen)

[P_de_B]

Huurmoordenaars schreef op 19 december 2003 @ 13:04:
bigbeng, precies wat ik zocht, bedankt ! (iedereen)

maar let op dat eventuele indexen op volgnum niet worden gebruikt.

[whoami]

P_de_B schreef op 19 december 2003 @ 13:09:
[...]

maar let op dat eventuele indexen op volgnum niet worden gebruikt.

Waarom denk je dat?
Volgens mij gaat de index die daarop ligt wel gebruikt worden, aangezien je enkel wildcards toevoegt aan de rechterkant in je WHERE.

[Maasluip]

bigbeng schreef op 19 december 2003 @ 13:01:
Ik heb het volgende ff getest:

code:

SELECT Left(volgNum,10) AS Expr1, Sum(volgNumHoeveelheid.hoeveelheid) AS SomVanhoeveelheid
FROM volgNumHoeveelheid
WHERE (((volgNumHoeveelheid.volgnum) Like Left([volgNum],10) & '**'))
GROUP BY Left(volgNum,10);

Die whereclause is niet nodig. Omdat je toch groepeert op Left(volgNum,10) heb je al alles wat je wil.

[P_de_B]

whoami schreef op 19 december 2003 @ 13:24:
[...]

Waarom denk je dat?

Omdat je een functie ( LEFT() ) toepast op het veld, SQL Server gebruikt dan geen indexen meer. Ik neem aan dat Access dit ook niet doet.

[whoami]

Hij gebruikt idd een LEFT, maar hij doet dat niet op één van z'n tabel-velden in z'n where clausule, maar op het criteria-veld.
Mocht het idd zo zijn, dat er ook dan geen indexen gebruikt worden, dan kan je het alsnog omzeilen door eerst die substring te doen (in je client applicatie dus), en die substring dan door te geven als parameter aan je query.