[SQL] Problem mit doppelter Ergebnismenge

[MarkusB]

Hi Igotcha!

Dein Problem laesst sich mit Hilfe 3 Views loesen:

1. VIEW1 : Summen pro Position aus der "DATEN_IST" Tabelle (daten_ist_summe_pro_position)

SQL-Code:

SELECT position, sum(jan) AS jan0, 
                 sum(feb) AS feb0, 
                 sum(mar) AS mar0, 
                 sum(apr) AS apr0, 
                 sum(mai) AS mai0, 
                 sum(jun) AS jun0, 
                 sum(jul) AS jul0, 
                 sum(aug) AS aug0, 
                 sum(sep) AS sep0, 
                 sum(okt) AS okt0, 
                 sum(nov) AS nov0, 
                 sum(dez) AS dez0
FROM daten_ist, positionen
WHERE position=positionen.posid and pagid in ("PIM5231", "PIM5244") and positionen.pgid=2000
GROUP BY position;

Ergebnis siehe Anhang: view1.jpg

2. VIEW2 : Summen pro Position aus der "DATEN_PROGNOSE" Tabelle (daten_prognose_summe_pro_position)

SQL-Code:

SELECT position, sum(jan) AS jan0, 
                 sum(feb) AS feb0, 
                 sum(mar) AS mar0, 
                 sum(apr) AS apr0, 
                 sum(mai) AS mai0, 
                 sum(jun) AS jun0, 
                 sum(jul) AS jul0, 
                 sum(aug) AS aug0, 
                 sum(sep) AS sep0, 
                 sum(okt) AS okt0, 
                 sum(nov) AS nov0, 
                 sum(dez) AS dez0, 
                 sum(jan_1) AS jan1, 
                 sum(feb_1) AS feb1, 
                 sum(mar_1) AS mar1, 
                 sum(apr_1) AS apr1, 
                 sum(mai_1) AS mai1, 
                 sum(jun_1) AS jun1
FROM daten_prognose, positionen
WHERE position=positionen.posid and pagid in ("PIM5231", "PIM5244") and positionen.pgid=2000
GROUP BY position;

Ergebnis siehe Anhang: view2.jpg

3. View3: Summen pro Position aus VIEW1 und VIEW2

SQL-Code:

SELECT i.position, i.jan0 as jani, 
                   i.feb0 as febi, 
                   i.mar0 as mari, 
                   i.apr0 as apri, 
                   i.mai0 as maii, 
                   i.jun0 as juni, 
                   i.jul0 as juli, 
                   p.aug0 as augp, 
                   p.sep0 as sepp, 
                   p.okt0 as oktp, 
                   p.nov0 as novp, 
                   p.dez0 as dezp
FROM daten_ist_summe_pro_position AS i, daten_prognose_summe_pro_position AS p
WHERE i.position=p.position;

Ergebnis siehe Anhang: view3.jpg

Viele Grüße
Markus