SQL: UNION-resultaten gesommeerd weergeven

[De doorloper]

Ik heb een probleem waar ik niet uitkom. Heb al gezocht en diverse oplossingen gevonden, maar krijg het niet voor elkaar om ze toe te passen zonder dat MySql 4.0.24 het afkeurt.

De database bevat uitslagen van sportwedstrijden waarbij ik een tabel "speler" heb en een tabel "wedstrijd". Hierbij bevat de tabel "wedstrijd" de velden "speler1" en "speler2" die beide verwijzen naar speler.id.

Wat ik wil, is een overzicht van alle spelers die gespeeld hebben, gesorteerd op de totaal behaalde punten aflopend, gevolgd door het aantal gespeelde wedstrijden oplopend. Ik heb het volgende al voor elkaar:

Query:

code:

SELECT s.voornaam, s.achternaam, COUNT(*) AS Aantal, SUM(won1) AS Punten
FROM fg_wedstrijd w INNER JOIN fg_speler s ON (w.speler1=s.id)
GROUP BY s.id

UNION

SELECT s.voornaam, s.achternaam, COUNT(*) AS Aantal, SUM(won2) AS Punten
FROM fg_wedstrijd w INNER JOIN fg_speler s ON (w.speler2=s.id)
GROUP BY s.id

ORDER BY Punten DESC, Aantal ASC

Output:

code:

Voornaam | Achternaam | Gespeeld | Punten
Piet     | Krediet    | 3        | 6
Jan      | Klaassen   | 1        | 3
Piet     | Krediet    | 5        | 15
Jan      | Klaassen   | 3        | 1

Wat ik in plaats hiervan graag wil zien, is het volgende:

code:

Voornaam | Achternaam | Gespeeld | Punten
Piet     | Krediet    | 8        | 21
Jan      | Klaassen   | 4        | 4

Oftewel: van het huidige resultaat moeten de punten en aantallen van dezelfde speler steeds gesommeerd terugkomen.

Gevonden voorbeelden in de structuur met geneste query's gaan fout op de geneste query terwijl 4.0.24 volgens MySql.com wel geneste query's ondersteunt. Maar misschien doe ik toch iets verkeerd. Wordt er wel moe van, maar misschien kan iemand een query ophoesten die doet wat ik wil . Anders wordt het twee query's en nabewerken in PHP maar als het niet hoeft...

[Verwijderd]

Meest snelle oplossing:

SQL:

SELECT s.voornaam, s.achternaam, SUM(Aantal) AS Aantal_2, SUM(Punten) AS Punten FROM 
(SELECT s.voornaam, s.achternaam, COUNT(*) AS Aantal, SUM(won1) AS Punten
FROM fg_wedstrijd w INNER JOIN fg_speler s ON (w.speler1=s.id)
GROUP BY s.id

UNION

SELECT s.voornaam, s.achternaam, COUNT(*) AS Aantal, SUM(won2) AS Punten
FROM fg_wedstrijd w INNER JOIN fg_speler s ON (w.speler2=s.id)
GROUP BY s.id

ORDER BY Punten DESC, Aantal ASC)
GROUP BY s.voornaam, s.achternaam
ORDER BY Punten DESC, Aantal ASC)

Je group by is in de union ook niet goed... Je moet dus group by voornaam en achternaam doen.

Volgens mij kan het ook allemaal in 1 query... Twee innerjoins en dan geen union, scheelt een stuk performance.

[ Voor 8% gewijzigd door Verwijderd op 16-10-2006 14:45 ]

[De doorloper]

Daarop geeft mijn MySql de volgende fout

code:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT s . voornaam , s . achternaam , COUNT( * ) AS Aantal , S

Maar hoe wil je het dan met twee inner joins doen? Dat heb ik ook al geprobeerd, maar lukt me ook niet. Als ik vanuit de spelertabel twee joins naar de wedstrijdtabel doe, krijg ik absurde resultaten en als ik vanuit de wedstrijdtabel twee joins naar de spelertabel doe, wat mij beter lijkt, dan moet ik nog de som van twee verschillende velden uit de wedstrijdtabel bij elkaar optellen. De wedstrijdtabel zit er namelijk als volgt uit:

code:

id (primary key)
datum
speler1 (id naar spelertabel)
speler2 (id naar spelertabel)
won1 (legs gewonnen door speler1)
won2 (legs gewonnen door speler2)

Het punt is dus dat spelers zowel in speler1 als in speler2 kunnen staan. Nooit tegelijk natuurlijk, maar ik moet dus de sum(won1) van speler1 optellen bij de sum(won2) van speler2.

[mulder]

SQL:

SELECT COUNT(*) AS Aantal, SUM(won1) AS Punten 
FROM fg_wedstrijd 
WHERE (w.speler1 = s.id OR w.speler2 = s.id) 

zou toch gewoon kunnen?

[ Voor 7% gewijzigd door mulder op 16-10-2006 15:21 ]

[De doorloper]

ja, maar dan moet je dus die query per speler uitvoeren en achteraf de sortering toepassen. Het zou juist zo mooi zijn als dat niet nodig zou zijn. Maar goed, ik ben me al aan het beraden op het naprutsen met arrays .
Overigens komt in die korte query precies het probleem naar boven: SUM(won1) is alleen geldig op de records waarbij de bedoelde speler als speler1 in de tabel staat. Staat deze speler als speler2 in de tabel, dan geldt SUM(won2).

[ Voor 34% gewijzigd door De doorloper op 16-10-2006 15:29 ]

[mulder]

MySQL heeft geen functions? Ik neem wel aan dat SUM(won1) + SUM(won2) werkt?

[ Voor 47% gewijzigd door mulder op 16-10-2006 15:37 ]

[De doorloper]

Nee dat werkt niet. Beide SUMS zijn conditioneel en als ik daarvoor twee aliassen gebruik, krijg ik resultaten die veel te hoog zijn. Maar ik denk dat ik mijn eerste query ga gebruiken en dan in php maar een array maak die ik aan het eind sorteer...