GROUP BY: Het juiste gebruik ervan
- Inleiding
- Uitleg GROUP BY en gebruikte basisgegevens
- Fout gebruik van de GROUP BY clausule
- GROUP BY en verzamelingsfuncties
- Groeperen op meerdere kolommen
- GROUP BY en HAVING
- GROUP BY Modifiers
- Slotwoord en referenties
- Reacties op deze tutorial
Groeperen op meerdere kolommen
Het kan voorkomen dat je een tabel hebt waarin je verschillende individuele groepen kunt creeren. Dan kom je zeer waarschijnlijk op het punt dat je wilt gaan groeperen op meerdere kolommen. Gelukkig is dit helemaal geen probleem met GROUP BY.Allereerst weer even de basisgegevens die we gebruiken:
Code
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
+----+-------+--------+------------+
| id | naam | aantal | datum |
+----+-------+--------+------------+
| 1 | Piet | 115 | 2007-07-11 |
| 2 | Karel | 230 | 2007-07-11 |
| 3 | Piet | 45 | 2007-07-11 |
| 4 | Kees | 135 | 2007-07-11 |
| 5 | Tom | 190 | 2007-07-12 |
| 6 | Karel | 150 | 2007-07-12 |
| 7 | Kees | 30 | 2007-07-12 |
| 8 | Piet | 90 | 2007-07-12 |
+----+-------+--------+------------+
| id | naam | aantal | datum |
+----+-------+--------+------------+
| 1 | Piet | 115 | 2007-07-11 |
| 2 | Karel | 230 | 2007-07-11 |
| 3 | Piet | 45 | 2007-07-11 |
| 4 | Kees | 135 | 2007-07-11 |
| 5 | Tom | 190 | 2007-07-12 |
| 6 | Karel | 150 | 2007-07-12 |
| 7 | Kees | 30 | 2007-07-12 |
| 8 | Piet | 90 | 2007-07-12 |
+----+-------+--------+------------+
Stel nu dat ik het totaal aantal punten per speler, per dag zou willen weten. Daarvoor moet ik allereerst groeperen op de naam, maar vervolgens ook op de datum.
Voorbeeld 8: Aantal punten per speler per dag
Code
1
2
3
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum
FROM punten
GROUP BY naam, datum
Dit geeft het volgende resultaat:
Code
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
+-------+------------+--------+
| naam | datum | totaal |
+-------+------------+--------+
| Karel | 2007-07-11 | 230 |
| Karel | 2007-07-12 | 150 |
| Kees | 2007-07-11 | 135 |
| Kees | 2007-07-12 | 30 |
| Piet | 2007-07-11 | 160 |
| Piet | 2007-07-12 | 90 |
| Tom | 2007-07-12 | 190 |
+-------+------------+--------+
| naam | datum | totaal |
+-------+------------+--------+
| Karel | 2007-07-11 | 230 |
| Karel | 2007-07-12 | 150 |
| Kees | 2007-07-11 | 135 |
| Kees | 2007-07-12 | 30 |
| Piet | 2007-07-11 | 160 |
| Piet | 2007-07-12 | 90 |
| Tom | 2007-07-12 | 190 |
+-------+------------+--------+
We zien hier dat nu netjes het totaal aantal punten per speler per dag weergegeven wordt. Natuurlijk kunnen we hier ook weer alle andere verzamelingsfuncties op toepassen.
Voor het volgende voorbeeld gaan we even uit van een statistiekensysteem. Ik heb een tabel 'statistieken' waarin ik elke keer een nieuw record aanmaak als een gebruiker mijn website bezoekt. De naam van de gebruiker zet ik in de kolom 'gebruikersnaam' en de datum van het bezoek zet ik in de kolom 'datum'.
Voorbeeld 9: Aantal bezoeken per dag
Code
1
2
3
2
3
SELECT datum, COUNT(*) AS aantal
FROM statistieken
GROUP BY datum
FROM statistieken
GROUP BY datum
In deze query gebruik ik COUNT() om het aantal records te tellen. Ik hoef niet speciaal een kolomnaam te definieren in de COUNT(). Sterker nog, dit is het enige geval waarin het * snellere resultaten levert.
Het resultaat van deze query is dus het aantal bezoeken per dag. Maar nu ben ik ook geinteresseerd hoe vaak elke bezoeker per dag op mijn site is geweest.
Voorbeeld 10: Aantal bezoeken per gebruiker per dag
Code
1
2
3
2
3
SELECT naam, datum, COUNT(*) AS aantal
FROM statistieken
GROUP BY naam, datum
FROM statistieken
GROUP BY naam, datum
Net als in voorbeeld 8 groepeer ik hier op meerdere kolommen om het gewenste resultaat te verkrijgen.
In de inleiding sprak ik al over het feit dat MySQL de standaard SQL regels wat losser interpreteert. Dat is hier ook het geval. Standaard SQL zegt dat, in een query met een GROUP BY clausule, alle kolommen uit de SELECT waar geen verzamelingsfunctie op toegepast is, terug moeten komen in de GROUP BY clausule. In MySQL hoeft dat echter niet.
Standaard SQL:
Code
1
2
3
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum
FROM punten
GROUP BY naam, datum
MySQL:
Code
1
2
3
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam
FROM punten
GROUP BY naam
Je kunt echter alleen kolommen uit de GROUP BY weglaten als je absoluut zeker weet dat de waarden per groep in die kolom allemaal hetzelfde zijn. Ander loop je tegen het zeer grote probleem aan dat MySQL weer random waarden terug gaat sturen.
voorbeeld 11: Foute waarden als gevolg van een onjuiste GROUP BY
Code
1
2
3
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam
FROM punten
GROUP BY naam
Dit geeft de volgende resultaten:
Code
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
+-------+------------+--------+
| naam | datum | totaal |
+-------+------------+--------+
| Karel | 2007-07-11 | 380 |
| Kees | 2007-07-11 | 165 |
| Piet | 2007-07-11 | 250 |
| Tom | 2007-07-12 | 190 |
+-------+------------+--------+
| naam | datum | totaal |
+-------+------------+--------+
| Karel | 2007-07-11 | 380 |
| Kees | 2007-07-11 | 165 |
| Piet | 2007-07-11 | 250 |
| Tom | 2007-07-12 | 190 |
+-------+------------+--------+
Deze resultaten doen vermoeden dat Karel op 11-7-2007 een totaal aantal punten van 380 behaald heeft. Terwijl volgens onze basisgegevens en de resultaten uit voorbeeld 8 maar 230 is.
Het is mogelijk MySQL zo in te stellen dat dit soort queries geweigerd worden. Je zult de systeem variabele sql_mode aan moeten passen en 'ONLY_FULL_GROUP_BY' als criterium toe moeten voegen:
Code
1
SET GLOBAL slq_mode = 'ONLY_FULL_GROUP_BY'
Dit is echter lang niet op alle hosts mogelijk aangezien je hier bepaalde privileges voor nodig hebt. Houdt je dus liever gewoon aan de SQL standaard om te voorkomen dat je onbetrouwbare resultaten uit de database krijgt.
Voor meer informatie over het instellen van de modus waarin MySQL draait, kijk je in de MySQL handleiding.
