MySQL: Berechnete Spalte

[Bloob]

Hallo Forum,

ich bin Webmaster in einem Sportverein. Für unsere Statistiken habe ich ein Tool programmiert, in dem für jedes Spiel bestimmte Daten eingefügt werden (Anzahl Tore, Torschütze, Assist, Strafzeiten etc).
Nun geht es ans Auswerten der Statistiken. Gerade bin ich dabei, die durchschnittliche Tore pro Spiel des jeweiligen Spielers auszurechnen. Dafür muss man natürlich die Spiele bei denen der Spieler anwesend war durch die geschossenen Toren des Spielers dividieren.

Folgenden SQL habe ich schon zusammengebastelt:

  
SELECT name, count( * ) AS anz_spiele,  
(  
 SELECT sum( 1 )  
 FROM stat_tore  
 WHERE stat_tore.spieler_id = sz.spieler_id  
)  
anz_tore  
FROM stat_szuordnung sz  
LEFT JOIN stat_spieler sp ON sz.spieler_id = sp.ID  
GROUP BY sz.spieler_id  

Dieser Code gibt mir als Ergebnis den Namen (dazu ist der Left Join da), die Anzahl der Spiele bei denen der Spieler anwesend war (anz_spiele), und die Anzahl der geschossenen Tore des Spielers (anz_tore).

Natürlich möchte ich nun die beiden letzten Spalten dividieren. Dazu füge ich dem SQL eine Zeile nach "anz_tore" hinzu, woraus dann folgender SQL entsteht:

  
SELECT name, count( * ) AS anz_spiele,  
(  
 SELECT sum( 1 )  
 FROM stat_tore  
 WHERE stat_tore.spieler_id = sz.spieler_id  
)  
anz_tore,  
anz_tore/anz_spiele schnitt  
FROM stat_szuordnung sz  
LEFT JOIN stat_spieler sp ON sz.spieler_id = sp.ID  
GROUP BY sz.spieler_id  

Doch daraus resultiert folgender Fehler:

#1054 - Unknown column 'anz_tore' in 'field list'

Sprich, MySQL kennt das Feld anz_tore nicht. Das stimmt ja auch, in der Tabelle an sich gibt es diese Spalte nicht. Doch irgendwie muss es doch möglich sein, diese beiden Spalten zu dividieren. Wie müsste das dann aussehen?

Lg
Bloob

[Ilja]

yo,

ich bin Webmaster in einem Sportverein.

um welchen sport geht es den ?

Dieser Code gibt mir als Ergebnis den Namen (dazu ist der Left Join da),

jedes andere dbms würde schon bei der ersten anweisung eine fehlermeldung ausgeben. der grund liegt darin, dass du spalten ausgibst, über du die du nicht gruppiert hast und keine aggregat-funktion benutzen. nur mysql geht da einen anderen weg. du bist wohl inzwischen der 1. millionste mysql nutzer, der darauf reinfällt.....

ich denke deine abfrage ist recht trivial, aber erkläre doch erst mal deine tabellen, welche relevanten daten sie haben und mit beispieldaten bitte. das ist immer besser, als abfragen zu analysieren.

Ilja

[Bloob]

Hi

ich bin Webmaster in einem Sportverein.
um welchen sport geht es den ?

Hockey. Aber ist das wichtig?

jedes andere dbms würde schon bei der ersten anweisung eine fehlermeldung ausgeben. der grund liegt darin, dass du spalten ausgibst, über du die du nicht gruppiert hast und keine aggregat-funktion benutzen. nur mysql geht da einen anderen weg. du bist wohl inzwischen der 1. millionste mysql nutzer, der darauf reinfällt.....

aha? Für mich sieht das sehr in Ordnung aus. Was meinst du eigentlich? Den Substring?

ich denke deine abfrage ist recht trivial, aber erkläre doch erst mal deine tabellen, welche relevanten daten sie haben und mit beispieldaten bitte. das ist immer besser, als abfragen zu analysieren.

Okay:

stat_spieler(ID, name, beendet)                       #Tabelle aller Spieler die im Verein sind
stat_tore(ID, spiel_id, tor_nr, spieler_id)           #Wer hat die Tore geschossen?
stat_assists(ID, tor_id, spieler_id)                  #Wer hat die Assists zu den Toren geschossen?
stat_szuordnung(ID, spieler_id, spiel_id, pos)        #Wer war in welchem Spiel anwesend und auf welcher Position (Torwart oder Spieler)?
stat_strafminuten(ID, spieler_id, spiel_id, anzahl)   #Wer hat in welchem Spiel wieviele Strafminuten erhalten?

Ich glaube man merkt, dass ich zwar theoretische Erfahrung im Datenbankdesign habe, aber in der Praxis weniger Erfahrung besitze. Ich habe gelernt, dass man (vor allem in diesem Fall) den Primärschlüssel aus zwei oder mehr Feldern bestehen lassen kann - nur wie ich das in MySQL realisiere, ist mir noch schleierhaft. Deshalb habe ich mich (erstmal) für IDs entschieden.
Fremd- und Primärschlüssel ergeben sich, denke ich, aus den Bezeichnungen der Felder.

Ich könnte den Durchschnitt auch per PHP ausrechnen lassen, doch ich möchte lieber der Datenbank das überlassen.

Lg
Bloob

[Hopsel]

Hi Bloob!

ich bin Webmaster in einem Sportverein.
um welchen sport geht es den ?
Hockey. Aber ist das wichtig?

Ja! MySQL unterstützt im Moment nur Schach und Halma. Die Entwickler arbeiten zwar gerade an einer neuen Version, die auch Teamsportarten unterstützt, aber bis dahin ist es noch ein weiter Weg.

So weit ich weiß gibt es aber eine inoffizielle Version für Ballsportarten. Diese nennt sich MyBQL: My Balls Query Language!

=)

MfG H☼psel

[Ilja]

yo,

Hockey. Aber ist das wichtig?

ja, abfragen sind immer abhängig von dem datendesign, dort werden schon oftmals fehler gemacht, was dann die abfragen komplizierter macht. selbst für profis ist ein gutes design nicht trivial. es lohnt also immer ein blick darauf zu werfen, welche fachlichkeit eigentlich abgebildet werden soll. hilft auch immer zum verstädnis des problems.

aha? Für mich sieht das sehr in Ordnung aus. Was meinst du eigentlich? Den Substring?

genau das, was ich gesagt habe. du gibst spalten in einer gruppierung aus, die keine aggregatfunktion verwenden und nicht in der gruppierung sind, in deinem falle die spalte name.

zum besseren verständnis gebe ich dir ein beispiel. nehmen wir an, wir haben eine einfachte tabelle mit nachnamen und vornamen von kunden. nun mache ich eine gruppierung über den nachnamen, um herauszubekommen, wieiviel Müllers und Meiers ich in der tabelle habe.

SELECT nachname, COUNT(*) Anzahl
FROM tabelle
GROUP BY nachname
;

so weit kein problem. nun verwende ich aber den "mysql-bug-its-not-a-feature", indem ich noch die spalten vorname hinzufüge, ohne aber darüber zu gruppieren. und dann wid auch schon das problem für das dbms klar, welche der vielen möglichen vornamen (Horst, Egon, Peter...), die den gleichen nachnamen haben (sagen wir Müller), soll das dbms den anzeigen ? mysql nimmt in solchen fällen irgendeinen wert der zufällig gewählt wird. das schlimme daran ist, dadurch dass mysql keine fehlermeldung aus gibt fällt es den meisten benutzern gar nicht auf, das was falsch läuft.

nun aber zu deiner abfrage. ich benutze sehr gerne unterabfragen (in deinem falle genauer gesagt korrelierte unterabfragen), in der äußeren FROM klausel nehme ich tabellen, die mir die richtige anzahl an datensätze liefern, die ich zurück haben will.

SELECT s.id, s.name,
       (SELECT COUNT(*)
        FROM stat_szuordnung z2
        WHERE z2.spieler_id = s.id
       ) AnzahlSpiele,
       (SELECT COUNT(*)
        FROM stat_tore t2
        WHERE t2.spieler_id = s.id
       ) AnzahlTore,
FROM stat_spieler s
;

damit hast du schon mal den namen der spieler, seine anzahl der spiele und die anzahl der tore, damit kannst du erst mal weiter probieren, bedenke aber nicht durch 0 zu teilen.

Ilja