Count Query
Ik heb het volgende:
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:
Code (php)
Hopelijk kunnen jullie me wat verder helpen.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
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
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.
Gewijzigd op 23/09/2013 21:50:42 door Erwin H
Bedankt voor het meedenken iniedergeval!
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.
Tipje (ff reclame gheghe)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?
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
?>
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
?>
Gewijzigd op 23/09/2013 22:09:02 door D B
Ik heb nu het volgende:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?
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'
?>
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?
Gewijzigd op 24/09/2013 10:39:47 door D B
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?
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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
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.
Wat sleur-pleur foutjes eruit gehaald.
Gewijzigd op 24/09/2013 12:58:44 door Ger van Steenderen
Alleen jij stelde dat het niet uit maakt waar de subquery staat, en dat is onjuist. Als je de volgorde in het voorbeeld omdraait krijg je een ander resultaat.
Gewijzigd op 24/09/2013 13:33:24 door Ger van Steenderen
Dit heb ik nu: (ik dacht met een COALESCE te kunnen werken, maar dit lijkt niet goed)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<?
SELECT
lid.voornaam,
lid.achternaam,
lid.team,
lid.id,
COALESCE(COUNT(g.leden_id), 0) AS aantal_goals
FROM
(SELECT
l.id,
l.voornaam,
l.achternaam,
t.team
FROM
Leden AS l
JOIN
Teamindeling AS ti
ON
(l.id = ti.leden_id)
JOIN
Teams AS t
ON
(t.id = ti.team_id)
WHERE
ti.taak = 'Speler'
AND
ti.seizoen = '2013/2014'
AND
t.team = 'ZSC 5'
GROUP BY
l.id,
l.voornaam,
l.achternaam
) AS lid
LEFT JOIN
Goals AS g
ON
(lid.id = g.leden_id) AND g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
LEFT JOIN
Wedstrijden AS w
ON
(g.wedstrijd_id = w.id)
WHERE
w.soort = 'Competitie'
GROUP BY
lid.voornaam,
lid.achternaam,
lid.team
ORDER BY
aantal_goals DESC
?>
SELECT
lid.voornaam,
lid.achternaam,
lid.team,
lid.id,
COALESCE(COUNT(g.leden_id), 0) AS aantal_goals
FROM
(SELECT
l.id,
l.voornaam,
l.achternaam,
t.team
FROM
Leden AS l
JOIN
Teamindeling AS ti
ON
(l.id = ti.leden_id)
JOIN
Teams AS t
ON
(t.id = ti.team_id)
WHERE
ti.taak = 'Speler'
AND
ti.seizoen = '2013/2014'
AND
t.team = 'ZSC 5'
GROUP BY
l.id,
l.voornaam,
l.achternaam
) AS lid
LEFT JOIN
Goals AS g
ON
(lid.id = g.leden_id) AND g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
LEFT JOIN
Wedstrijden AS w
ON
(g.wedstrijd_id = w.id)
WHERE
w.soort = 'Competitie'
GROUP BY
lid.voornaam,
lid.achternaam,
lid.team
ORDER BY
aantal_goals DESC
?>
Maar wat je niet goed doet is een WHERE op een kolom uit een tabel via een left join. Daarmee vervalt het effect van de left join want je filtert de rijen waarvan w.soort NULL zijn eruit.
Oplossing is een geneste join of een subquery.
Geneste join:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
.....
LEFT JOIN
(goals g
INNER JOIN
wedstrijden w
ON (g.wedstrijd_id = w.id) AND (w.soort = 'Competitie')
)
ON
(lid.id = g.leden_id) AND g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
LEFT JOIN
(goals g
INNER JOIN
wedstrijden w
ON (g.wedstrijd_id = w.id) AND (w.soort = 'Competitie')
)
ON
(lid.id = g.leden_id) AND g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
Subquery:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
LEFT JOIN
(SELECT
leden_id
FROM goals
JOIN wedstrijden w
ON goals.wedstrijd_id = w.id
WHERE
g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
AND w.soort = 'Competitie') g
(SELECT
leden_id
FROM goals
JOIN wedstrijden w
ON goals.wedstrijd_id = w.id
WHERE
g.seizoen = '2013/2014' AND g.wie LIKE 'ZSC%'
AND w.soort = 'Competitie') g
Ook is de eerste subquery niet meer nodig, die was alleen nodig om te voorkomen dat leden die in meerdere teams spelen dubeel geselecteerd worden, maar nu je het per team doet is dat niet meer nodig.
Als ik die subquery in die left join gebruik, moet ik dan ook nog een ON hebben?
Ik heb nu dit: (krijg 1 speler te zien :))
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?
$query = "
SELECT
l.voornaam,
l.achternaam,
COUNT(go.leden_id) aantal_goals
FROM
Leden AS l
LEFT JOIN
(SELECT
leden_id
FROM
Goals AS g
JOIN
Wedstrijden AS w
ON
g.wedstrijd_id = w.id
WHERE
g.seizoen = '". $seizoen. "' AND g.wie LIKE 'ZSC%'
AND
w.soort = 'Competitie')
AS go
ON
(l.id = go.leden_id)
JOIN
Teamindeling AS t
ON
(l.id = t.leden_id)
JOIN
Teams AS team
ON
(team.id = t.team_id)
WHERE
t.taak = 'Speler'
AND
t.seizoen = '". $seizoen. "'
AND
t.team_id = '". $teamid. "'
ORDER BY
aantal_goals DESC
";
?>
$query = "
SELECT
l.voornaam,
l.achternaam,
COUNT(go.leden_id) aantal_goals
FROM
Leden AS l
LEFT JOIN
(SELECT
leden_id
FROM
Goals AS g
JOIN
Wedstrijden AS w
ON
g.wedstrijd_id = w.id
WHERE
g.seizoen = '". $seizoen. "' AND g.wie LIKE 'ZSC%'
AND
w.soort = 'Competitie')
AS go
ON
(l.id = go.leden_id)
JOIN
Teamindeling AS t
ON
(l.id = t.leden_id)
JOIN
Teams AS team
ON
(team.id = t.team_id)
WHERE
t.taak = 'Speler'
AND
t.seizoen = '". $seizoen. "'
AND
t.team_id = '". $teamid. "'
ORDER BY
aantal_goals DESC
";
?>
En jij bent de GROUP BY vergeten, zonder dat groepeer je de gehele recordset en krijg je dus maar één record