[SQL] resultaat sum over twee ongelijke tabellen in 1 query

[aardbeix15]

Beste mensen,

ik worstel met een SQL vraagstukje en kom er, na hulp van vrienden en collega's maar niet op.

ten eerste:

tabel1

code:

periode    waarde
200307    3
200308    2
200308    1
200308    3
200308    6
200309    3

tabel2

code:

periode    waarde
200308    2
200308    1
200308    3
200308    6

tabel2 is een sub-set van tabel1. Ter controle wil ik een sum over de waarden doen in beide tabellen en dit samen als volgt weergeven:
tabel 1

code:

tabel1    tabel2
12          12

de waarden moeten vallen binnen de periode 200308 van tabel1, bij tabel2 natuurlijk ook echter dat is een sub-set dus die voldoet altijd.

met de volgende query lukt het niet:

code:

SELECT Sum([Tabel1.Waarde]), Sum([Tabel2.Waarde])
FROM Tabel1, Tabel2
WHERE Tabel1.Periode=200308

het lijkt wel of i ongewenst gaat vermenigvuldigen etc. een Cartiaans product na allerlei INNER, LEFT JOINS en sub-queries te hebben geprobeert ben ik er nog steeds niet achter.

Ik heb hem nu als 3 aparte queries lopen omdat ik er niet uitkwam, echter in 1 is toch het mooist. Wie kan me helpen:?

[Creepy]

Je zult toch echt moeten joinen. Dit kan je doen door in je where de periode van tabel1 te vergelijken met de periode van tabel2.

[jvdmeer]

Even snel uit m'n hoofd:

SQL:

SELECT i.periode, SUM(i.waarde), SUM(j.waarde) FROM
(
  SELECT Periode,waarde FROM tabel1
) i,
(
  SELECT Periode,waarde FROM tabel2
) j
WHERE i.periode=j.periode
GROUP BY i.periode

[aardbeix15]

Creepy schreef op 18 september 2003 @ 08:34:
Je zult toch echt moeten joinen. Dit kan je doen door in je where de periode van tabel1 te vergelijken met de periode van tabel2.

dat werk niet, zo bedoel je

code:

SELECT Sum([Tabel1.Waarde]), Sum([Tabel2.Waarde])
FROM Tabel1, Tabel2
WHERE Tabel1.Periode=8 AND Tabel1.Periode = Tabel2.Periode

ik krijg dan hetzelfde resultaat als mijn eerste query

wanneer ik

code:

SELECT Sum([Tabel1.Waarde]), Sum([Tabel2.Waarde])
FROM Tabel1, Tabel2
WHERE Tabel1.Periode=8 AND Tabel1.Periode = Tabel2.Periode AND Tabel1.Waarde=Tabel2.Waarde

uitvoer krijg ik tweemaal een fout resultaat

[ Voor 20% gewijzigd door aardbeix15 op 18-09-2003 08:40 ]

[aardbeix15]

jvdmeer schreef op 18 September 2003 @ 08:37:
Even snel uit m'n hoofd:

SQL:

SELECT i.periode, SUM(i.waarde), SUM(j.waarde) FROM
(
  SELECT Periode,waarde FROM tabel1
) i,
(
  SELECT Periode,waarde FROM tabel2
) j
WHERE i.periode=j.periode
GROUP BY i.periode

ook dan treed dat Cartiaans product op, het resultaat is:

code:

periode Expr1001    Expr1002
8   3740    4092

sorry waarden zijn iets anders in mijn testtabel, dan ik heb gepost, maar het klopt in ieder geval niet

[ Voor 11% gewijzigd door aardbeix15 op 18-09-2003 08:44 ]

[Creepy]

aardbeix15 schreef op 18 September 2003 @ 08:39:
[...]


dat werk niet, zo bedoel je

code:

SELECT Sum([Tabel1.Waarde]), Sum([Tabel2.Waarde])
FROM Tabel1, Tabel2
WHERE Tabel1.Periode=8 AND Tabel1.Periode = Tabel2.Periode

ik krijg dan hetzelfde resultaat als mijn eerste query

Dat bedoelde ik ja, aangezien je over een chartechiaans produkt begon
Dus wat gaat er dan mis? Wat voor waarden krijg je nu? Of kijk ff naar de query van jvdmeer

Of selecteer de boel eens zonder de sums, en kijk eens of er uit komt wat je verwacht.

Edit: Ow, wacht es ff. Dat gaat inderdaad niet werken. Je vergelijkt nu alleen de periodes, je zult ook de waardes moeten vergelijken. Anders krijg je inderdaad per entry in tabel 1, alle entry's van tabel 2 in diezelfde periode. Hierbij ga ik er wel vanuit dat ALLE waarden van tabel1 ook in tabel2 voorkomen. Anders is de query van jvdmeer de enige correcte (en dan hoop ik niet dat je nog een oude MySQL draait). Blijkbaar is die ook niet helemaal correct.

[ Voor 37% gewijzigd door Creepy op 18-09-2003 08:47 ]

[aardbeix15]

Creepy je raakt nu idd net de kern van mijn probleem, wat als een ogenschijnlijk simpele query begon wordt iets heel moeilijks.

in het geval van de query hierboven krijg je voor ebide tabellen de waarde 48 terug, terwijl deze toch echt 12 zou moeten zijn. Uit andere tests met grotere databases blijkt de verklaring dat deze sumwaarde (12) wordt vermenigvuldigd met het aantal records uit de subset (4)

[aardbeix15]

zo werkt i bijna

code:

SELECT 
Sum([Tabel1.Waarde]), 
Sum([Tabel2.Waarde])
FROM 
Tabel1, 
Tabel2
WHERE 
Tabel1.Periode=200308 
AND 
Tabel1.Periode=Tabel2.Periode 
AND  
Tabel1.Waarde=Tabel2.Waarde

echter nu wordt in beide resultaatcellen altijd het aantal uit tabel1 weergegeven (getest door waarden in beide tabbel te laten verschillen, anders dan de waarden in de startpost dus.

[ Voor 3% gewijzigd door aardbeix15 op 18-09-2003 09:00 ]

[Bufiku]

Omdat de tabellen geen afhankelijkheid hebben, maar redundante informatie opgeslagen hebben, kun je het waarschijnlijk niet in één statement oplossen. Je mist namelijk een gemeenschappelijke key om vanuit tabel_1 één record uniek te benaderen. Je krijgt altijd, met een join, een vermenigvuldiging van het aantal entries uit de gejoinde tabel as de keys identiek zijn. Je zult het in twee selects moeten doen.

<edit>
@ aardbeix15: in jouw oplossing hierboven ga je ervan uit dat de waarde een key veld is. Zou ik niet doen. De vraag was om de totalen te vergelijken, nu controleer je alleen welke entries in beide tabellen voorkomen. Mocht een entry in tabel 1 staan en niet in tabel 2, komt deze niet voor in jouw query.
</edit>

[ Voor 31% gewijzigd door Bufiku op 18-09-2003 09:04 ]

[whoami]

Je kan natuurlijk met een UNION gaan werken, mits je DBMS dat ondersteunt.
Je krijgt je gegevens dan wel in deze vorm:
resultat tabel1
resultaat tabel2

Maar, dat zou niet erg mogen zijn aangezien SQL niet bedoeld is voor de opmaak van je gegevens, enkel voor het ophalen en manipuleren ervan.

code:

SELECT "tabel1", sum(aantal) from tabel1
UNION
SELECT "tabel2", sum(aantal) from tabel2

[aardbeix15]

tsja die UNION kende ik ook al, maar goed dat is niet echt een fraaie oplossing, én wanneer de resultaten gelijk zijn gooit UNION er 1 weg en geeft i nog maar 1 cel weer, beetje irritant omdat ik altijd tweemaal resultaat wil zien, ook al zijn deze gelijk.

[Bufiku]

Of misschien als volgt? Ik ken deze SQL-syntax niet, (ken alleen de SAP subset van OPEN_SQL subset, waar de volgende syntax niet mogelijk is)

SELECT i.periode, i.waarde, j.waarde FROM
(
SELECT Periode, SUM (waarde) FROM tabel1
) i,
(
SELECT Periode, SUM(waarde) FROM tabel2
) j
WHERE i.periode=j.periode
GROUP BY i.periode

[ Voor 4% gewijzigd door Bufiku op 18-09-2003 09:17 . Reden: taalfout ]

[Arnaud]

SELECT Table1.periode, Sum(Table1.waarde) AS SumOfwaardeTable1, (SELECT Sum(Table2.waarde) FROM Table2 GROUP BY Table2.periode HAVING (((Table2.periode)=Table1.periode));) AS SumOfwaardeTable2
FROM Table1
GROUP BY Table1.periode;

[Verwijderd]

SELECT ( select SUM(table2.waarde) from table2 where periode = "200308" ), SUM ( table1.waarde ) FROM table1 WHERE periode = "200308"

[Bufiku]

Nog twee vragen:
- Welk SQL-dialect spreek je?
- Moet het programma wat je schrijft onderhouden worden? Als dat zo is, zou ik niet voor een high-tech één-statement oplossing gaan, want dan staat er volgend jaar een post met wat doet deze code?

[aardbeix15]

Verwijderd schreef op 18 september 2003 @ 09:14:
SELECT ( select SUM(table2.waarde) from table2 where periode = "200308" ), SUM ( table1.waarde ) FROM table1 WHERE periode = "200308"

dit is hem, met enige aanpassing gebruik ik hem nu zo

code:

SELECT (select SUM(tabel2.waarde) from tabel2 ), SUM ( tabel1.waarde ) FROM tabel1 WHERE periode =200308

tnhx

[Johnny Goodbye]

whoami schreef op 18 September 2003 @ 09:06:
Je kan natuurlijk met een UNION gaan werken, mits je DBMS dat ondersteunt.
Je krijgt je gegevens dan wel in deze vorm:
resultat tabel1
resultaat tabel2

Maar, dat zou niet erg mogen zijn aangezien SQL niet bedoeld is voor de opmaak van je gegevens, enkel voor het ophalen en manipuleren ervan.

code:

SELECT "tabel1", sum(aantal) from tabel1
UNION
SELECT "tabel2", sum(aantal) from tabel2

Probeer dit eens:

SELECT "tabel1", sum(aantal) as tabel1, 0 as tabel2 from tabel1
UNION
SELECT "tabel2", 0 as tabel1, sum(aantal) as tabel2 from tabel2[/code][/quote]

dan kan je wel dit krijgen

tabel1 tabel2
12 0
0 12

[whoami]

aardbeix15 schreef op 18 September 2003 @ 09:07:
tsja die UNION kende ik ook al, maar goed dat is niet echt een fraaie oplossing, én wanneer de resultaten gelijk zijn gooit UNION er 1 weg en geeft i nog maar 1 cel weer, beetje irritant omdat ik altijd tweemaal resultaat wil zien, ook al zijn deze gelijk.

Je kan er natuurlijk voor zorgen dat je geen unieke rows hebt. In mijn voorbeeld bv, heb je die last niet, aangezien de 2 rows nooit uniek zullen zijn.

[aardbeix15]

check mijn eerdere post Oxy666 gaf de juiste query! de oplossing is dus gevonden

[whoami]

aardbeix15 schreef op 18 September 2003 @ 09:23:
check mijn eerdere post Oxy666 gaf de juiste query! de oplossing is dus gevonden

Dat zag ik wel, maar mag ik dan niet meer posten in dit topic?

Trouwens, krijg je met die query niet teveel resultaten? Ik bedoel, ipv 1 row terug te krijgen met het juiste resultaat, zal je n keer het resultaat terugkrijgen waar n het aantal records is waar periode = 200308

[aardbeix15]

ik d8 ff dat dat niet duidelijk was vandaar verder geen probleem

ik krijg niet te veel rijen terug, gewoon 1 record over 2 kolommen met in cel 1 e sum van tabel 1 en in cel 2 de sum van tabel 2, precies wat mijn wens was.

[ Voor 196% gewijzigd door aardbeix15 op 18-09-2003 09:34 ]

[Arnaud]

De query van Oxy666 doet inderdaad precies wat je vraagt. Ik had je vraag niet goed genoeg gelezen en dacht dat je alle perioden wilde controleren. Mocht dat toch je bedoeling zijn geweest, gebruik dan de query die ik hierboven ook al had staan:

code:

SELECT Table1.periode, Sum(Table1.waarde) AS SumOfwaardeTable1, 
  (SELECT Sum(Table2.waarde) 
  FROM Table2 GROUP BY Table2.periode 
  HAVING (((Table2.periode)=Table1.periode));) AS SumOfwaardeTable2
FROM Table1
GROUP BY Table1.periode;

[aardbeix15]

geen sub-query dus maar een zgn. inline-query