Probleem met COUNT
Ik maak een voetbalsite en wil uitslagen bijhouden van wedstrijden.
Nu heb ik de volgende SQL-query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
SELECT rugnummer, achternaam,
(SELECT count( tbl_doelpunten.spelerID )
FROM tbl_doelpunten
LEFT JOIN tbl_opstellingn ON tbl_doelpunten.spelerID = tbl_opstellingn.spelerID
WHERE thuis_uit = 'thuis'
AND tbl_doelpunten.wedstrijdID = '109') AS aantaldoelpunten
FROM tbl_opstellingn
LEFT JOIN tbl_spelers ON tbl_opstellingn.spelerID = tbl_spelers.spelerID
LEFT JOIN ktbl_club_speler ON tbl_opstellingn.spelerID = ktbl_club_speler.spelerID
WHERE wedstrijdID = '109'
AND thuis_uit = 'thuis'
(SELECT count( tbl_doelpunten.spelerID )
FROM tbl_doelpunten
LEFT JOIN tbl_opstellingn ON tbl_doelpunten.spelerID = tbl_opstellingn.spelerID
WHERE thuis_uit = 'thuis'
AND tbl_doelpunten.wedstrijdID = '109') AS aantaldoelpunten
FROM tbl_opstellingn
LEFT JOIN tbl_spelers ON tbl_opstellingn.spelerID = tbl_spelers.spelerID
LEFT JOIN ktbl_club_speler ON tbl_opstellingn.spelerID = ktbl_club_speler.spelerID
WHERE wedstrijdID = '109'
AND thuis_uit = 'thuis'
En dit geeft onderstaande als resultaat:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
rugnummer achternaam aantaldoelpunten
1 Coutinho 1
15 Malone 1
3 Wormgoor 1
18 Beugelsdijk 1
8 Meijers 1
7 Jansen 1
14 Visser 1
10 Toornstra 1
11 Chery 1
36 Vicento 1
9 Poepon 1
24 de Zwart 1
33 Horvath 1
5 Supusepa 1
16 Mulders 1
21 Tano 1
17 Elbers 1
19 van Duinen 1
1 Coutinho 1
15 Malone 1
3 Wormgoor 1
18 Beugelsdijk 1
8 Meijers 1
7 Jansen 1
14 Visser 1
10 Toornstra 1
11 Chery 1
36 Vicento 1
9 Poepon 1
24 de Zwart 1
33 Horvath 1
5 Supusepa 1
16 Mulders 1
21 Tano 1
17 Elbers 1
19 van Duinen 1
Dat klopt niet aangezien alleen speler Vicento heeft gescoord. Hoe zorg ik ervoor dat alleen bij speler Vicento er een '1' staat bij het aantal doelpunten en bij de andere spelers '0'?
Met andere woorden: hoe zorg ik ervoor dat de count voor elke speler afzonderlijk wordt uitgevoerd?
Gewijzigd op 23/10/2013 15:20:54 door Jo Immanuel
Die count zal je uit moeten voeren als subquery in een join, waarbij je groepeert op speler om de totalen per speler te krijgen.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT COUNT(*) AS aantaldoelpunten, tbl_doelpunten.spelerID
FROM tbl_doelpunten
LEFT JOIN tbl_opstellingn ON tbl_doelpunten.spelerID = tbl_opstellingn.spelerID
WHERE thuis_uit = 'thuis'
AND tbl_doelpunten.wedstrijdID = '109'
GROUP BY tbl_doelpunten.spelerID
FROM tbl_doelpunten
LEFT JOIN tbl_opstellingn ON tbl_doelpunten.spelerID = tbl_opstellingn.spelerID
WHERE thuis_uit = 'thuis'
AND tbl_doelpunten.wedstrijdID = '109'
GROUP BY tbl_doelpunten.spelerID
Hier komen alleen de spelers uit die ook een record in de tabel hebben, met andere woorden alleen die spelers die daadwerkelijk gescoord hebben. Dat is verder geen probleem, want in je hoofd query haal je alle spelers op en middels een LEFT JOIN voeg je deze toe. Alle spelers die niet gescoord hebben krijgen dus wel een record in de hoofd query, maar gewoon niets (NULL) voor het aantal doelpunten. Daar kan je dan weer een 0 van maken via de functie COALESCE.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT rugnummer, achternaam, COALESCE(aantaldoelpunten, 0) AS aantaldoelpunten
FROM tbl_opstellingn
LEFT JOIN tbl_spelers ON tbl_opstellingn.spelerID = tbl_spelers.spelerID
LEFT JOIN ktbl_club_speler ON tbl_opstellingn.spelerID = ktbl_club_speler.spelerID
LEFT JOIN (
SELECT COUNT(*) AS aantaldoelpunten, tbl_doelpunten.spelerID
FROM tbl_doelpunten
LEFT JOIN tbl_opstellingn ON tbl_doelpunten.spelerID = tbl_opstellingn.spelerID
WHERE thuis_uit = 'thuis'
AND tbl_doelpunten.wedstrijdID = '109'
GROUP BY tbl_doelpunten.spelerID
) a ON tbl_spelers.spelerID = a.spelerID
WHERE wedstrijdID = '109'
AND thuis_uit = 'thuis'
FROM tbl_opstellingn
LEFT JOIN tbl_spelers ON tbl_opstellingn.spelerID = tbl_spelers.spelerID
LEFT JOIN ktbl_club_speler ON tbl_opstellingn.spelerID = ktbl_club_speler.spelerID
LEFT JOIN (
SELECT COUNT(*) AS aantaldoelpunten, tbl_doelpunten.spelerID
FROM tbl_doelpunten
LEFT JOIN tbl_opstellingn ON tbl_doelpunten.spelerID = tbl_opstellingn.spelerID
WHERE thuis_uit = 'thuis'
AND tbl_doelpunten.wedstrijdID = '109'
GROUP BY tbl_doelpunten.spelerID
) a ON tbl_spelers.spelerID = a.spelerID
WHERE wedstrijdID = '109'
AND thuis_uit = 'thuis'
Gewijzigd op 23/10/2013 15:28:01 door Erwin H
Nou hartstikke bedankt. Ik krijg nu de resultaten die ik hebben wil.
Zit ik wel met een volgend issue.
Ik wil uiteindelijk niet bij het aantal doelpunten een '0', '1' of '2' etc hebben staan, maar een pictogrammetje van een bal dat een doelpunt aangeeft.
Dus wanneer een speler niet gescoord heeft alleen zn rugnummer en achternaam. Heeft een speler 1x gescoord dan 1 voetballetje achter het rugnummer en achternaam. Heeft een speler 2x gescoord dan twee voetballen naast elkaar. Hoe pak ik zoiets aan?
PS: Je hebt overigens gelijk gekregen met je advies destijds dat ik mijn opstellingstabel moest aanpassen en voor elke spelerID een apart record moest maken in de tabel opstellingen (ipv een hele hoop verschillende spelerID's in 1 opstellingrecord).
Toevoeging op 23/10/2013 18:32:23:
Toevoeging:
Om in plaats van "aantaldoelpunten" de icoontjes (voetballen) te krijgen om zo het aantal gescoorde doelpunten van een speler te laten zien heb ik nu de CASE-functie gebruikt.
Dus dan wordt het begin van de query zo:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT rugnummer, achternaam,
CASE
WHEN aantaldoelpunten = 1 THEN '<img src="1bal.png" alt="icon" />'
WHEN aantaldoelpunten = 2 THEN '<img src="2bal.png" alt="icon" />'
WHEN aantaldoelpunten = 3 THEN '<img src="3bal.png" alt="icon" />'
WHEN aantaldoelpunten = 4 THEN '<img src="4bal.png" alt="icon" />'
WHEN aantaldoelpunten = 5 THEN '<img src="5bal.png" alt="icon" />'
WHEN aantaldoelpunten = 6 THEN '<img src="6bal.png" alt="icon" />'
WHEN aantaldoelpunten = 7 THEN '<img src="7bal.png" alt="icon" />'
WHEN aantaldoelpunten = 8 THEN '<img src="8bal.png" alt="icon" />'
WHEN aantaldoelpunten = 9 THEN '<img src="9bal.png" alt="icon" />'
END AS aantaldoelpunten
FROM tbl_opstellingn
enz.
CASE
WHEN aantaldoelpunten = 1 THEN '<img src="1bal.png" alt="icon" />'
WHEN aantaldoelpunten = 2 THEN '<img src="2bal.png" alt="icon" />'
WHEN aantaldoelpunten = 3 THEN '<img src="3bal.png" alt="icon" />'
WHEN aantaldoelpunten = 4 THEN '<img src="4bal.png" alt="icon" />'
WHEN aantaldoelpunten = 5 THEN '<img src="5bal.png" alt="icon" />'
WHEN aantaldoelpunten = 6 THEN '<img src="6bal.png" alt="icon" />'
WHEN aantaldoelpunten = 7 THEN '<img src="7bal.png" alt="icon" />'
WHEN aantaldoelpunten = 8 THEN '<img src="8bal.png" alt="icon" />'
WHEN aantaldoelpunten = 9 THEN '<img src="9bal.png" alt="icon" />'
END AS aantaldoelpunten
FROM tbl_opstellingn
enz.
En vervolgens verschillende plaatjes gemaakt, waarbij bij plaatje "img src="1bal/png" alt="icon"" er 1 voetbal is, bij plaatje 2bal/png twee voetballen naast elkaar zijn, enz.
Om geen plaatje te krijgen als een speler niet heeft gescoord heb ik de coalesce-functie weer weggehaald om zo juist well NULL als resultaat te krijgen als een speler niet gescoord heeft.
Dan krijg ik het volgende resultaat:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
rugnummer achternaam aantaldoelpunten
1 Coutinho NULL
15 Malone NULL
3 Wormgoor NULL
18 Beugelsdijk NULL
8 Meijers NULL
7 Jansen NULL
14 Visser NULL
10 Toornstra NULL
11 Chery NULL
36 Vicento <img src="1bal.png" alt="icon" />
9 Poepon NULL
24 de Zwart NULL
33 Horvath NULL
5 Supusepa NULL
16 Mulders NULL
21 Tano NULL
17 Elbers NULL
19 van Duinen NULL
1 Coutinho NULL
15 Malone NULL
3 Wormgoor NULL
18 Beugelsdijk NULL
8 Meijers NULL
7 Jansen NULL
14 Visser NULL
10 Toornstra NULL
11 Chery NULL
36 Vicento <img src="1bal.png" alt="icon" />
9 Poepon NULL
24 de Zwart NULL
33 Horvath NULL
5 Supusepa NULL
16 Mulders NULL
21 Tano NULL
17 Elbers NULL
19 van Duinen NULL
Wat goed lijkt. Toch doet het op me zelf een beetje houtje-tauwtje aan allemaal hoe ik het nu heb aangepakt. Iemand een logischere/betere suggestie?
Gewijzigd op 23/10/2013 15:43:29 door Jo Immanuel