SQL: Joins beim Einsatz von Aggregat-Funktionen verwenden

Aus DBWiki
Wechseln zu: Navigation, Suche

Zurück zur Übersicht



Bei der Berechnung von Aggregat-Funktionen über Joins muss man vorsichtig sein. Wenn Dubletten, Tripletten, usw. wegen eines Joins zurückgegeben werden, können diese zu Fehlberechnungen führen. Dazu kann man in den meisten SQL-Dialekten das Schlüsselwort DISTINCT innerhalb der Funktion verwenden, welches dafür sorgt, dass ein Wert nur einmal bei Berechnungen herangezogen wird. Eine Alternative besteht in der Verwendung von Inline-Abfragen, in denen das Aggregat vor dem Verknüpfen ermittelt wird.

Lösung für Jet-SQL

Hinweis für Access-Anwender
Um die folgenden Abfragen selbst testen zu können, müssen die Tabellen der Übungsdatenbank vorhanden sein.


Als Ausgangslage verwenden wir hier mithilfe der Tabelle dual eine der Tabelle praemie gleiche Struktur, die wir durch eine Vereinigungsabfrage vw_paeamie simulieren.

SELECT 7934 AS ma_nr, #2005-03-17# AS erhalten, 1 AS typ FROM dual
UNION  ALL
SELECT 7934, #2005-02-15#, 2 FROM dual
UNION  ALL
SELECT 7839, #2005-02-15#, 3 FROM dual
UNION  ALL
SELECT 7782, #2005-02-15#, 1 FROM dual
ma_nr erhalten typ
7934 17.03.2005 1
7934 15.02.2005 2
7839 15.02.2005 3
7782 15.02.2005 1

So soll z.B. die Summe der Gehälter der Mitarbeiter der Abteilung 10 mit der Summe ihrer Prämien ermittelt werden. Einige Mitarbeiter haben mehr als eine Prämie, und der Join zwischen Tabelle mitarbeiter und Tabelle vw_bonus führt dazu, dass falsche Werte von der Aggregatfunktion SUM zurückgegeben werden.

Bedenke nun die folgende Situation, die das Gehalt und die Prämie für alle Mitarbeiter der Abteilung 10 zurückgibt. Der typ bestimmt die Höhe der Prämie, wobei eine 1 10% des Gehalts, eine 2 20% und eine 3 30% des Gehalts entspricht.

SELECT m.ma_nr,
       ma_name,
       gehalt,
       abt_nr,
       gehalt * IIf( typ = 1, 0.1,
                     IIf( typ = 2, 0.2, 0.3 ) ) AS praemie
FROM   mitarbeiter AS m,
       vw_praemie AS p
WHERE  m.ma_nr = p.ma_nr AND 
       abt_nr = 10

Das Ergebnis sieht richtig aus.

ma_nr ma_name gehalt abt_nr praemie
7934 Müller 1300 10 130
7934 Müller 1300 10 260
7839 König 5000 10 1500
7782 Kühne 2450 10 245

Schief geht es dann, wenn man versucht, diese Abfrage mittels Join zu verknüpfen, um die Summe über die Abteilung zu berechnen.

SELECT abt_nr,
       sum(gehalt) AS total_gehalt,
       sum(praemie) AS total_praemie
FROM   ( SELECT m.ma_nr,
                ma_name,
                gehalt,
                abt_nr,
                gehalt * IIf( typ = 1, 0.1,
                              IIf( typ = 2, 0.2, 0.3 ) ) AS praemie
         FROM   mitarbeiter AS m,
                vw_praemie AS p
         WHERE  m.ma_nr = p.ma_nr AND 
                abt_nr = 10 ) AS q
GROUP  BY abt_nr
abt_nr total_gehalt total_praemie
10 10050 2135

Ergebnis: Das total_gehalt ist falsch berechnet.

SELECT sum(gehalt)
FROM   mitarbeiter
WHERE  abt_nr = 10

Gibt den richtigen Wert von 8750 aus. Die zwei Prämien von Müller führen dazu, dass auch sein Gehalt zweimal summiert wird.


Hier folgt die eigentliche Lösung:

SELECT abt_nr,
       ( SELECT sum(gehalt)
         FROM   mitarbeiter
         WHERE  abt_nr = q.abt_nr) AS total_gehalt,
       sum(praemie) AS total_praemie
FROM   ( SELECT m.ma_nr,
                ma_name,
                gehalt,
                abt_nr,
                gehalt * IIf( typ = 1, 0.1,
                              IIf( typ = 2, 0.2, 0.3 ) ) AS praemie
         FROM   mitarbeiter AS m,
                vw_praemie AS p
         WHERE  m.ma_nr = p.ma_nr AND 
                abt_nr = 10 ) AS q
GROUP  BY abt_nr

Dieses Mal mit richtigem total_gehalt der Abteilung:

abt_nr total_gehalt total_praemie
10 8750 2135

Lösung für SQLite3, PostgreSQL, ...

SELECT abt_nr,
       SUM(DISTINCT gehalt) AS total_gehalt,
       SUM(praemie) AS total_praemie
FROM   ( SELECT m.ma_nr,
                ma_name,
                gehalt,
                abt_nr,
                gehalt * CASE WHEN typ = 1 THEN 0.1
                              WHEN typ = 2 THEN 0.2
                              ELSE              0.3
                         END AS praemie
         FROM   mitarbeiter AS m,
                vw_praemie AS p
         WHERE  m.ma_nr = p.ma_nr AND 
                abt_nr = 10 ) AS q
GROUP  BY abt_nr

Hier ist ein dazugehörender DBFiddle.



Zurück zur Übersicht