DB: Problem mit SUM() in Subselect

[Nico R.]

Hallo zusammen,

ich möchte zu einem Spieler alle Saisons namentlich auflisten, der der Spieler zugeordnet ist (Tabelle saison_spieler). Zusätzlichen sollen die an den einzelnen Spieltagen der jeweiligen Saison gesamt gespielten Minuten, Tore etc. angezeigt werden. Hört sich eigentlich banal an, aber ich hänge irgendwie...

Ich habs auf diesem Weg, per Subselect, versucht:

SELECT 		
	saison_spieler.saison_id AS saison_id,
	saison.name AS saison_name,
	saison.jahr AS saison_jahr,
    stat.spielzeit
FROM 
	`saison_spieler`
JOIN
	`saison`
    ON(saison.id = saison_spieler.saison_id) 
JOIN
	(SELECT 
     	spieltag.saison_id AS saison_id_,
		SUM(spielerstatistik.spielzeit) AS spielzeit
    FROM
    	spielerstatistik 
    JOIN
    	spieltag
        ON(spieltag.id = spielerstatistik.spieltag_id)
    WHERE 
		spielerstatistik.spieler_id = 12
    ) AS stat    
    ON(stat.saison_id_ = saison_id)
WHERE
	saison_spieler.spieler_id = 12
GROUP BY
	saison_id

Das Ergebnis:

Mein Problem ist die Spalte 14. Das SUM() sorgt dafür, dass das Ergebnis aus dem Haupt-SELECT auf nur eine Ergebnis-Zeile eingedampft wird. Die Summe 36517 sind die gespielten Minuten aus allen Saisons. Entferne ich das SUM() aus dem Subselect, bekomme ich dies als Ergebnis:

Jetzt werden die Saisons korrekt aufgelistet, aber die gespielten Minuten sind nur die des ersten Spiels, das der Spieler in der jeweiligen Saison absolviert hat. Das ist ja insofern richtig, als dass keine Gesamtsumme gebildet wird und im Haupt-SELECT nur "Platz" für ein Ergebnis aus dem Subselect ist.

Aber wie erhalte ich denn nun die korrekte Summe aus dem Subselect? Ich habe es auf verschiedenen Wegen versucht. Die jetzt alle aufzuzählen, würde den Rahmen sprengen. Der obige Ansatz scheint mir doch der Vielversprechendste zu sein. Vielleicht hat ja jemand einen Denkanstoß, der mich weiterbringt.

Schöne Grüße und vielen Dank schonmal an alle, die sich die Mühe machen, sich da reinzudenken, das finde ich bei DB-Problemen immer besonderns mühsam.

Nico

[MudGuard]

Hi,

	(SELECT 
     	spieltag.saison_id AS saison_id_,
		SUM(spielerstatistik.spielzeit) AS spielzeit
    FROM
    	spielerstatistik 
    JOIN
    	spieltag
        ON(spieltag.id = spielerstatistik.spieltag_id)
    WHERE 
		spielerstatistik.spieler_id = 12
    ) AS stat    

eine aggregierte Spalte (SUM) und eine nicht-aggregierte (saison): da hätte ich dann noch ein GROUP BY erwartet.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Nico,

du machst das, was alle MYSQL-Verwender tun: sie ignorieren den SQL Standard.

Die Grundregel ist: Eine Spalte ist aggregiert (also SUM oder COUNT) oder nicht. Wenn ich aggregierte und nicht-aggregierte Spalten mischen will, dann muss ich nach den nicht-aggregierten Spalten gruppieren. Alles andere ist ein logischer Fehler.

Der Bullshit bei MYSQL ist, dass das Fehlen von GROUP BY nicht ankreidet. Statt dessen wählt es für nicht-aggregierte Spalten einfach irgendeinen Wert aus dieser Spalte aus, zumeist den ersten.

Das ruiniert Dir deinen Teil-Select für die Spielzeit:

SELECT
   spieltag.saison_id AS saison_id_,
   SUM(spielerstatistik.spielzeit) AS spielzeit
FROM
   spielerstatistik 
JOIN
   spieltag
        ON(spieltag.id = spielerstatistik.spieltag_id)
WHERE 
   spielerstatistik.spieler_id = 12

Ein ordentlicher SQL Server würde Dir das um die Ohren hauen, weil Du spieltag.saison_id in der SELECT Liste hast, nach dieser Spalte aber nicht gruppierst. MYSQL will „anfängerfreundlich“ und fehlertolerant sein und nimmt einfach die ID von irgendeiner Zeile. Bei Dir ist es die Saison-ID 9.

Das ist nicht anfängerfreundlich. Es ist schlichtweg verwirrender Blödsinn. MYSQL hat keinerlei Anhaltspunkt dafür, aus welcher Zeile es die ID nehmen könnte. Ein korrekt implementierter SQL Server darf dieses Statement nicht ausführen.

Lösung: Verschiebe die GROUP BY Klausel in den Teilselect für die Spielzeiten. Einen Aliasnamen für die Saison-ID brauchst Du da übrigens nicht. Klammern um die ON-Bedingung brauchst Du auch nicht.

Dann bekommst Du aus dem Subselect nicht nur eine Zeile, sondern eine pro Saison und je Saison die Summe der Spielzeiten, und welche Summe es auch sonst noch sein soll. Das Gruppieren auf oberster Ebene ist dann nicht mehr nötig.

Ich habe die Alias-Namen mal verkürzt, damit die Zeilen nicht zu lang werden. Eigentlich müsste man die Abfrage auf die Spieler-ID aus dem Stat-Subselect herausziehen, um die redundante Verwendung der 12 loszuwerden, aber ich weiß nicht, ob MYSQL das dann noch gut optimiert.

SELECT 		
       S_Sp.saison_id AS saison_id,
       saison.name    AS saison_name,
       saison.jahr    AS saison_jahr,
       Stat.spielzeit, Stat.einsätze, Stat.fouls
FROM
       saison_spieler AS S_Sp
JOIN
       saison
       ON saison.id = S_Sp.saison_id
JOIN
      (SELECT   T.saison_id,
                SUM(Sp_Stat.spielzeit) AS spielzeit,
                COUNT(*) AS einsätze,
                SUM(Sp_Stat.fouls) AS fouls
       FROM     spielerstatistik Sp_Stat
       JOIN     spieltag T ON T.id = Sp_Stat.spieltag_id
       WHERE    Sp_Stat.spieler_id = 12
       GROUP BY T.saison_id
      ) AS Stat    
      ON Stat.saison_id_ = S_Sp.saison_id 
WHERE
      S_Sp.spieler_id = 12

Ich hoffe, ich habe mich nicht vertippt - ich kann's ja nicht testen.

Rolf

[Nico R.]

Hallo Rolf, hallo Andreas,

danke sehr. Ja, es lag am falsch zugeordneten GROUP BY. Das gehörte nicht ins Hauptselect, sondern ins Subselect. So funktioniert die Abfrage jetzt:

SELECT 		
	saison_spieler.saison_id AS saison_id,
	saison.name AS saison_name,
	saison.jahr AS saison_jahr,
    stat.spielzeit
FROM 
	`saison_spieler`
JOIN
	`saison`
    ON saison.id = saison_spieler.saison_id
JOIN
	(SELECT 
     	spieltag.saison_id AS saison_id_,
		SUM(spielerstatistik.spielzeit) AS spielzeit
    FROM
    	spielerstatistik 
    JOIN
    	spieltag
        ON spieltag.id = spielerstatistik.spieltag_id
    WHERE 
		spielerstatistik.spieler_id = 12
	GROUP BY
		saison_id     
    ) AS stat    
    ON saison_id_ = saison_id
WHERE
	saison_spieler.spieler_id = 12

Die langen Tabellennamen habe ich beibehalten, weil ich sonst irgendwann gar nicht mehr durchsehe, T.saison_id ist mir zu abstrakt.

Ich hab auch den Alias saison_id_ im Subselect gelassen, weil es sowohl mit spieltag.saison_id ("Unbekanntes Tabellenfeld") als auch mit stat.saison_id ("Feld 'saison_id' in on clause ist nicht eindeutig") Fehlermeldungen gab.

Schöne Grüße

Nico

[Nico R.]

Ich hab auch den Alias saison_id_ im Subselect gelassen, weil es sowohl mit spieltag.saison_id ("Unbekanntes Tabellenfeld") als auch mit stat.saison_id ("Feld 'saison_id' in on clause ist nicht eindeutig") Fehlermeldungen gab.

Mit ON stat.saison_id = saison_spieler.saison_id funktioniert es. Ich lasse jetzt überflüssige Aliase für die Tabellenspalten weg, die waren noch Überbleibsel aus einem älteren Script.

SELECT 		
	saison_spieler.saison_id,
	saison.name AS saison_name,
	saison.jahr AS saison_jahr,
    stat.spielzeit
FROM 
	`saison_spieler`
JOIN
	`saison`
    ON saison.id = saison_spieler.saison_id
JOIN
	(SELECT 
     	spieltag.saison_id,
		SUM(spielerstatistik.spielzeit) AS spielzeit
    FROM
    	spielerstatistik 
    JOIN
    	spieltag
        ON spieltag.id = spielerstatistik.spieltag_id
    WHERE 
		spielerstatistik.spieler_id = 12
	GROUP BY
		saison_id     
    ) AS stat    
    ON stat.saison_id = saison_spieler.saison_id
WHERE
	saison_spieler.spieler_id = 12

Schöne Grüße

Nico