select s.nachname, s.vorname, s.team, s.bild, sp, sum(tore) as tore, sum(assist) as assist, sum(spiele) as spiele, sum(tore)+sum(assist) as punkte, sum(tore)/sum(spiele) as torschnitt, sum(assist)/sum(spiele) as assistschnitt, (sum(tore)+sum(assist))/sum(spiele) as punktschnitt from ( SELECT a.spieler as sp, count(*) as tore, 0 as assist, 0 as spiele FROM whl_spiel_ablauf as a LEFT JOIN whl_spiel as s on s.id=a.spiel WHERE a.aktion=1 and (s.saison=57 or s.saison=56 or s.saison=55) GROUP BY a.spieler union all SELECT a.assist as sp, 0 as tore, count(*) as assist, 0 as spiele FROM whl_spiel_ablauf as a LEFT JOIN whl_spiel as s on s.id=a.spiel WHERE a.aktion=1 and (s.saison=57 or s.saison=56 or s.saison=55) and a.assist>0 GROUP BY a.assist union all SELECT u.spieler as sp, 0 as tore, 0 as assist, count(*) as spiele FROM whl_spiel_spieler as u LEFT join whl_spiel as s on s.id=u.spiel WHERE (s.saison=57 or s.saison=56 or s.saison=55) GROUP BY u.spieler ) u LEFT JOIN whl_spieler as s on s.id=sp group by sp order by punkte desc, tore desc, punktschnitt desc limit 0, 10