Groeperen op meerdere kolommen

Het kan voorkomen dat je een tabel hebt waarin je verschillende individuele groepen kunt creƫren. 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 (php)
PHP script in nieuw venster Selecteer het PHP script
1
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 |
+----+-------+--------+------------+

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 (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum

Dit geeft het volgende resultaat:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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    |
+-------+------------+--------+

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 (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT datum, COUNT(*) AS aantal
FROM statistieken
GROUP BY datum

In deze query gebruik ik COUNT() om het aantal records te tellen. Ik hoef niet speciaal een kolomnaam te definiƫren 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 (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT naam, datum, COUNT(*) AS aantal
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 (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum


MySQL:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT naam, datum, SUM(aantal) AS totaal
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 (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam

Dit geeft de volgende resultaten:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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    |
+-------+------------+--------+

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 (php)
PHP script in nieuw venster Selecteer het PHP script
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.

« Lees de omschrijving en reacties

Inhoudsopgave

  1. Inleiding
  2. Uitleg GROUP BY en gebruikte basisgegevens
  3. Fout gebruik van de GROUP BY clausule
  4. GROUP BY en verzamelingsfuncties
  5. Groeperen op meerdere kolommen
  6. GROUP BY en HAVING
  7. GROUP BY Modifiers
  8. Slotwoord en referenties

PHP tutorial opties

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.