Een tabel Goals met daarin alle goals die gevallen zijn zoals onderstaand:
id-wedstrijd_id-leden_id-minuut
1-2-29-3
2-2-55-5
3-2-55-10
Nu wil ik alle goals van 1 persoon bij elkaar optellen en dan wil ik die goals ORDER BY maken.
Via de leden_id haal ik op in welk team het lid speelt, zodat ik daar later ook nog wat mee kan.
Maar als ik onderstaande query gebruik geeft hij aan dat bijvoorbeeld lid 29 4x gescoord heeft, dit komt -volgens mij- omdat het leden_id meerdere keren in Teamindeling voor komt, omdat deze persoon namelijk in meerdere teams speelt/coacht.
Ik heb het volgende geprobeerd:
<?
SELECT
l.voornaam,
l.achternaam,
COUNT(g.leden_id),
FROM
Goals as g
LEFT JOIN
Leden AS l
ON
(g.leden_id = l.id)
LEFT JOIN
Teamindeling AS t
ON
(g.leden_id = t.leden_id) AND t.seizoen = '2013/2014'
WHERE
g.leden_id != '0'
GROUP BY
g.leden_id
?>
Selecteer het aantal doelpunten eerst in een subquery en join daar de rest bij:
SELECT l.voornaam, l.achternaam, c.num
FROM (
SELECT leden_id, COUNT(*) AS num
FROM goals
GROUP BY leden_id
) c
LEFT JOIN Leden l ON c.leden_id = l.leden_id
ORDER BY c.num DESC
Wat me nu alleen niet helemaal duidelijk is, is waarom je de teamindeling er nog bij joint. Die gebruik je verder helemaal niet.
De teamindeling ga ik nog gebruiken om op te halen in welk team dat ze spelen, en dan om te sorteren op senioren en junioren teams. Ik ga nu kijken of je query werkt.
Je hebt nu precies de verkeerde volgorde.
Je gaat leden koppelen aan goals ipv van de goals aan leden.
Met andere woorden, je moet eerst de leden ophalen uit de leden tabel, daarna doe je een INNER JOIN op de team indeling, en dan pas ga je met een left join de goals erbij betrekken.
En natuurlijk moet je geen GROUP BY doen op:
a) een kolom die niet in de select list staat
b) een kolom die een geen waarde kan bevatten (indien je het in de juiste volgorde zet)
En helemaal officieel horen alle niet aggegrate kolommen in de select list ook in de group by te staan.
Nu probeer ik hem verder uit te werken, maar dat pikt hij dus niet :)
<?
SELECT
l.voornaam,
l.achternaam,
c.num,
l.id
FROM (
SELECT
leden_id,
COUNT(*) AS num
FROM
Goals
GROUP BY
leden_id
)
AS
c
LEFT JOIN
Leden AS l //Voor voor en achternaam
ON
(c.leden_id = l.id)
LEFT JOIN
Teams AS teams //Om te kijken voor welk team het is
ON
(g.wie = teams.team) AND teams.seizoen = '2013/2014'
WHERE
g.leden_id != '0'
AND
teams.leeftijd = 'Senioren'
ORDER BY
c.num DESC
?>
Ik heb nu het volgende:
<?
SELECT
l.voornaam,
l.achternaam,
teams.team,
count(g.leden_id)
FROM
Leden AS l
INNER JOIN
Teamindeling AS t
ON
(l.id = t.leden_id) AND t.seizoen = '2013/2014'
LEFT JOIN
Teams AS teams
ON
(t.team_id = teams.id)
LEFT JOIN
Goals AS g
ON
(l.id = g.leden_id) AND g.seizoen = '2013/2014'
WHERE
l.id = '29' //even om van 1 speler te testen
AND
t.taak = 'Speler'
?>
Maar nu heb ik dus uit meerdere teams in teamindeling, maar dat telt hij met de count ook bij elkaar op???
Terwijl ik natuurlijk alleen de goals wil tellen. Moet dit dan via een subquery ofzo?
Zolang je de doelpunten telt op een query waarin je al de teams hebt gejoined blijf je dat probleem houden. Wat mij betreft is de tip van Ger dan ook ofwel verkeerd (sorry Ger), ofwel niet voldoende geformuleerd. Het tellen van de doelpunten zal je apart moeten doen van het joinen van alle andere tabellen, anders blijf je verkeerd gaan. Dus in een subquery en of je die als hoofdtabel neemt, of dat je er later via een join bij zet is dan om het even.
Let overigens wel op dat als een speler in meerdere teams voorkomt, hij dus ook meerdere records krijgt in je uiteindelijke resultaat. Dit kan je alleen voorkomen als je een GROUP_CONCAT gebruikt: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
De goals staan gewoon per id in tabel Goals. Hoe zou ik deze dan (in mijn left join) met een subquery op kunnen laten tellen en meenemen in mijn 'hoofd'query?
@Erwin
Mijn tip c.q. opmerking ging over de volgorde, en die is bij een outer join cruciaal voor het uiteindelijke resultaat.
@DB
Als je dus per lid de goals wilt tellen (maar ook voor degenen die niet gescoord hebben), selecteer je vanuit de leden tabel. Maar omdat een lid bij meerdere teams kan spelen zal je met een subquery ervoor moeten zorgen dat je een view krijgt met per lid 1 record, daarna kan je gewoon de goals tabel left joinen:
SELECT
lid.voornaam,
lid.achternaam,
lid.teams,
COUNT(g.leden_id) aantal_goals
FROM
(SELECT
l.id,
l.voornaam,
l.achternaam,
GROUP_CONCAT(t.team) teams
FROM
leden l
JOIN
teamindeling ti
ON l.id = ti.leden_id
JOIN
teams t
ON t.id = ti.team_id
WHERE ti.taak = 'Speler' AND ti.seizoen = '2013/2014'
GROUP BY l.id, l.voornaam, l.achternaam
) lid
LEFT JOIN goals g
ON
lid.id = g.leden_id AND g.seizoen = '2013/2014'
GROUP BY
lid.voornaam, lid.achternaam, lid.teams
[edit]
Wat sleur-pleur foutjes eruit gehaald.
[/edit]
@Ger, dat bedoelde ik dus met dat je opmerking niet voldoende was. Omdat hij spelers heeft met links naar verschillende teams krijg je die spelers meerdere keren en als je daar simpelweg de goals aan gaat hangen dan gaat het fout. In dit complete voorbeeld heb je dat opgelost door de GROUP_CONCAT. In je eerdere opmerking stond daar niets over.
