Tutorials
GROUP BY: Het juiste gebruik ervan
Hoe gebruik je een GROUP BY clausule correct in je SQL query?
Pagina 1
Inleiding
De GROUP BY clausule in SQL is een normaal onderdeel van een query waarvan door veel mensen de werking niet begrepen wordt. In veel van de gevallen die ik tegenkom wordt een GROUP BY ten onrechte gebruikt om unieke resultaten te tonen. Dat er misschien unieke resultaten uit de query lijken te rollen is maar schijn, in werkelijkheid gebeurt er heel wat anders!
In deze tutorial zal ik proberen uit te leggen wat een GROUP BY clausule nou precies is en hoe deze gebruikt dient te worden. Allereerst zal ik een voorbeeld geven van het foute gebruik van een GROUP BY om vervolgens aan de hand van voorbeelden verder te gaan met de werking en het juiste gebruik ervan.
Nu is het probleem van MySQL dat het de standaard SQL regels op een aantal punten in deze tutorial veel losser interpreteert. Ik zal me in deze tutorial wel houden aan de standaard SQL en zal op sommige punten aangeven waar en hoe MySQL de regels anders interpreteert. Ik wil je aanraden om niet van de SQL standaard af te wijken, op die manier weet je zeker dat je queries met GROUP BY betrouwbare resultaten opleveren.
In deze tutorial ga ik er vanuit dat je de basis van MySQL al onder de knie hebt. Ik zal in voorbeelden alleen de gebruikte SQL queries tonen, het mag dan ook voor zichzelf spreken dat deze queries nog uitgevoerd moeten worden in een PHP script.
Benodigde voorkennis
- Basis PHP
- Werken met databases in PHP
- Basis MySQL
In deze tutorial zal ik proberen uit te leggen wat een GROUP BY clausule nou precies is en hoe deze gebruikt dient te worden. Allereerst zal ik een voorbeeld geven van het foute gebruik van een GROUP BY om vervolgens aan de hand van voorbeelden verder te gaan met de werking en het juiste gebruik ervan.
Nu is het probleem van MySQL dat het de standaard SQL regels op een aantal punten in deze tutorial veel losser interpreteert. Ik zal me in deze tutorial wel houden aan de standaard SQL en zal op sommige punten aangeven waar en hoe MySQL de regels anders interpreteert. Ik wil je aanraden om niet van de SQL standaard af te wijken, op die manier weet je zeker dat je queries met GROUP BY betrouwbare resultaten opleveren.
In deze tutorial ga ik er vanuit dat je de basis van MySQL al onder de knie hebt. Ik zal in voorbeelden alleen de gebruikte SQL queries tonen, het mag dan ook voor zichzelf spreken dat deze queries nog uitgevoerd moeten worden in een PHP script.
Benodigde voorkennis
- Basis PHP
- Werken met databases in PHP
- Basis MySQL
Pagina 2
Uitleg GROUP BY en gebruikte basisgegevens
Om duidelijk de werking van een GROUP BY clausule te laten zien, maak ik in deze tutorial gebruik van een tabel genaamd 'punten'. In deze tabel worden tijdens een meerdaagse wedstrijd de door deelnemers behaalde punten opgeslagen. Tevens wordt opgeslagen op welke datum ze die punten behaald hebben.
Wat betreft normlisatie is deze tabel natuurlijk geen hoogstandje, ik zou immers eigenlijk een aparte tabel voor de deelnemers moeten gebruiken. Echter voor het doel van deze tutorial is het een stuk duidelijker om de tabel op deze manier te gebruiken.
Wat een GROUP BY clausule in een query doet, is het groeperen van gegevens. Er wordt gegroepeert op alle kolommen die in de GROUP BY opgenomen zijn. Als ik bijvoorbeeld in bovenstaande tabel groepeer op de kolom 'naam' worden alle records waarvan de naam hetzelfde is, samengenomen.
Later in deze tutorial zal ik verder ingaan op de precieze werking van de GROUP BY. Nu zal ik eerst veel voorkomende foute gebruik van de GROUP BY clausule laten zien.
+----+-------+--------+------------+
| 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 |
+----+-------+--------+------------+
Wat betreft normlisatie is deze tabel natuurlijk geen hoogstandje, ik zou immers eigenlijk een aparte tabel voor de deelnemers moeten gebruiken. Echter voor het doel van deze tutorial is het een stuk duidelijker om de tabel op deze manier te gebruiken.
Wat een GROUP BY clausule in een query doet, is het groeperen van gegevens. Er wordt gegroepeert op alle kolommen die in de GROUP BY opgenomen zijn. Als ik bijvoorbeeld in bovenstaande tabel groepeer op de kolom 'naam' worden alle records waarvan de naam hetzelfde is, samengenomen.
Later in deze tutorial zal ik verder ingaan op de precieze werking van de GROUP BY. Nu zal ik eerst veel voorkomende foute gebruik van de GROUP BY clausule laten zien.
Pagina 3
Fout gebruik van de GROUP BY clausule
Zoals ik in de inleiding al vermelde, gebruiken veel mensen de GROUP BY clausule op een verkeerde manier. In de meeste gevallen wordt deze clausule gebruikt om unieke resultaten te tonen.
Allereerst nogmaals de basisgegevens die ik gebruik:
Voorbeeld 1: Fout gebruik GROUP BY
Deze query zal het volgende resultaat geven:
Nu lijkt het erop dat er inderdaad unieke gegevens getoond worden, maar in werkelijkheid is er iets anders gebeurd. De GROUP BY heeft gezorgd dat de resultaat gegroepeerd zijn op naam, dat wil zeggen dat alle records met dezelfde naam samengenomen zijn en getoond worden.
Het probleem is nu dat MySQL niet in staat is om deze groep in 1 record weer te geven. Het gevolg is dat MySQL 1 willekeurig record uit de groep kiest en weergeeft. Met de rest van de records uit die groep kun je nu niets meer beginnen.
Dit zal ik aantonen door de resultaten op het aantal punten te sorteren. Ik wil dat het aantal punten oplopend gesorteerd wordt.
Voorbeeld 2: Sorteren van de gegevens
Wat ik nu dus verwacht is dat elke naam maar 1 keer weergegeven wordt en dat tevens het laagste puntenaantal bij die naam getoond wordt. Maar dat is niet het resulaat:
Enkel het eerder verkregen resultaat is gesorteerd op het aantal punten. De 150 punten van bijvoorbeeld Karel zien we jammergenoeg nergens meer terug. Dit toont aan dat MySQL gewoon een willekeurig record uit de groep toont en de resutlaten dus niet betrouwbaar zijn.
Allereerst nogmaals de basisgegevens die ik gebruik:
+----+-------+--------+------------+
| 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 |
+----+-------+--------+------------+
Voorbeeld 1: Fout gebruik GROUP BY
SELECT naam, aantal
FROM punten
GROUP BY naam
Deze query zal het volgende resultaat geven:
+-------+--------+
| naam | aantal |
+-------+--------+
| Karel | 230 |
| Kees | 135 |
| Piet | 115 |
| Tom | 190 |
+-------+--------+
Nu lijkt het erop dat er inderdaad unieke gegevens getoond worden, maar in werkelijkheid is er iets anders gebeurd. De GROUP BY heeft gezorgd dat de resultaat gegroepeerd zijn op naam, dat wil zeggen dat alle records met dezelfde naam samengenomen zijn en getoond worden.
Het probleem is nu dat MySQL niet in staat is om deze groep in 1 record weer te geven. Het gevolg is dat MySQL 1 willekeurig record uit de groep kiest en weergeeft. Met de rest van de records uit die groep kun je nu niets meer beginnen.
Dit zal ik aantonen door de resultaten op het aantal punten te sorteren. Ik wil dat het aantal punten oplopend gesorteerd wordt.
Voorbeeld 2: Sorteren van de gegevens
SELECT naam, aantal
FROM punten
GROUP BY naam
ORDER BY aantal
Wat ik nu dus verwacht is dat elke naam maar 1 keer weergegeven wordt en dat tevens het laagste puntenaantal bij die naam getoond wordt. Maar dat is niet het resulaat:
+-------+--------+
| naam | aantal |
+-------+--------+
| Piet | 115 |
| Kees | 135 |
| Tom | 190 |
| Karel | 230 |
+-------+--------+
Enkel het eerder verkregen resultaat is gesorteerd op het aantal punten. De 150 punten van bijvoorbeeld Karel zien we jammergenoeg nergens meer terug. Dit toont aan dat MySQL gewoon een willekeurig record uit de groep toont en de resutlaten dus niet betrouwbaar zijn.
Pagina 4
GROUP BY en verzamelingsfuncties
De reden dat GROUP BY in MySQL geïntroduceerd is, is dat de verzamelingsfuncties zoals bijvoorbeeld SUM() en COUNT() altijd de verzameling van alle waarden uit een kolom retourneerden. Zonder de GROUP BY functie was het dus onmogelijk om bijvoorbeeld de som van een individuele groep waarden in een kolom te vinden.
Verzamelingsfuncties (engels: aggregate functions) zijn functies die een bewerking uitvoeren op een verzameling records en het resultaat retourneren. De belangrijkste functies zijn:
AVG() - Geeft het gemiddelde van een verzameling
COUNT() - Geeft het aantal waarden in een verzameling
MAX() - Geeft de maximale waarde uit een verzameling
MIN() - Geeft de minimale waarde uit een verzameling
SUM() - Geeft de som van de waarden in een verzameling
Ter referentie ook nog even de basisgegevens:
Voorbeeld 3: De som van het aantal punten
Deze query geeft als resultaat de som van het aantal punten terug. In dit geval is dat 985. Nu weet je echter alleen de som van het totaal aantal punten en nog niet wat de deelnemers individueel behaald hebben.
Stel dat we nu de volgende query proberen om ook de naam weer te geven.
Voorbeeld 4: De som van het aantal punten per deelnemer
Het uitvoeren van deze query geeft in de nieuwere versies van MySQL een foutmelding:
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
Deze foutmelding zegt dat het onmogelijk is om in een query normale kolommen te mixen met een kolom waarop een verzamelingsfunctie is toegepast, zonder dat er GROUP BY aan te pas komt.
Voorbeeld 5: De som van het aantal punten per deelnemer (2)
Deze query geeft het volgende resultaat:
We zien dat deze query inderdaad het totaal aantal punten per deelnemer weergeeft.
In de query van voorbeeld 5 kun je de SUM() functie natuurlijk vervangen door de andere verzamelingsfunctie. Elke functie geeft zo zijn eigen resultaat weer. Ik zal hier enkele voorbeelden van geven.
Voorbeeld 6: Het gemiddelde aantal punten per speler
Geeft het gemiddelde aantal punten per speler:
Voorbeeld 7: Het minimaal aantal punten per speler
Deze query is de oplossing van het probleem uit voorbeeld 2. Dit is de juiste manier om het minimaal aantal punten per speler op te halen:
Verzamelingsfuncties (engels: aggregate functions) zijn functies die een bewerking uitvoeren op een verzameling records en het resultaat retourneren. De belangrijkste functies zijn:
AVG() - Geeft het gemiddelde van een verzameling
COUNT() - Geeft het aantal waarden in een verzameling
MAX() - Geeft de maximale waarde uit een verzameling
MIN() - Geeft de minimale waarde uit een verzameling
SUM() - Geeft de som van de waarden in een verzameling
Ter referentie ook nog even de basisgegevens:
+----+-------+--------+------------+
| 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 |
+----+-------+--------+------------+
Voorbeeld 3: De som van het aantal punten
SELECT SUM(aantal) AS totaal
FROM punten
Deze query geeft als resultaat de som van het aantal punten terug. In dit geval is dat 985. Nu weet je echter alleen de som van het totaal aantal punten en nog niet wat de deelnemers individueel behaald hebben.
Stel dat we nu de volgende query proberen om ook de naam weer te geven.
Voorbeeld 4: De som van het aantal punten per deelnemer
SELECT naam, SUM(aantal) AS totaal
FROM punten
Het uitvoeren van deze query geeft in de nieuwere versies van MySQL een foutmelding:
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
Deze foutmelding zegt dat het onmogelijk is om in een query normale kolommen te mixen met een kolom waarop een verzamelingsfunctie is toegepast, zonder dat er GROUP BY aan te pas komt.
Voorbeeld 5: De som van het aantal punten per deelnemer (2)
SELECT naam, SUM(aantal) AS totaal
FROM punten
GROUP BY naam
Deze query geeft het volgende resultaat:
+-------+--------+
| naam | totaal |
+-------+--------+
| Karel | 380 |
| Kees | 165 |
| Piet | 250 |
| Tom | 190 |
+-------+--------+
We zien dat deze query inderdaad het totaal aantal punten per deelnemer weergeeft.
In de query van voorbeeld 5 kun je de SUM() functie natuurlijk vervangen door de andere verzamelingsfunctie. Elke functie geeft zo zijn eigen resultaat weer. Ik zal hier enkele voorbeelden van geven.
Voorbeeld 6: Het gemiddelde aantal punten per speler
SELECT naam, AVG(aantal) AS gemiddelde
FROM punten
GROUP BY naam
Geeft het gemiddelde aantal punten per speler:
+-------+------------+
| naam | gemiddelde |
+-------+------------+
| Karel | 190.0 |
| Kees | 82.5 |
| Piet | 83.3 |
| Tom | 190.0 |
+-------+------------+
Voorbeeld 7: Het minimaal aantal punten per speler
SELECT naam, MIN(aantal) AS minimum
FROM punten
GROUP BY naam
Deze query is de oplossing van het probleem uit voorbeeld 2. Dit is de juiste manier om het minimaal aantal punten per speler op te halen:
+-------+---------+
| naam | minimum |
+-------+---------+
| Karel | 150 |
| Kees | 30 |
| Piet | 45 |
| Tom | 190 |
+-------+---------+
Pagina 5
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:
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
Dit geeft het volgende resultaat:
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
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
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:
MySQL:
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
Dit geeft de volgende resultaten:
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:
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.
Allereerst weer even de basisgegevens die we gebruiken:
+----+-------+--------+------------+
| 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
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum
Dit geeft het volgende resultaat:
+-------+------------+--------+
| 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
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
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:
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum
MySQL:
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
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam
Dit geeft de volgende resultaten:
+-------+------------+--------+
| 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:
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.
Pagina 6
GROUP BY en HAVING
De HAVING clausule is net zoiets als een WHERE clausule en wordt in combinatie met een GROUP BY clausule gebruikt. Het verschil tussen HAVING en WHERE is dat je in een WHERE geen verzamelingsfuncties op mag nemen en in een HAVING wel.
Wederom eerst de basisgegevens waar we mee werken:
Voorbeeld 12: Deelnemers met een totaal aantal punten > 200
Dit geeft de volgende resultaten:
In dit geval zijn we gebonden aan het gebruik van HAVING omdat we vergelijking met een verzamelingsfunctie als criterium gebruiken. Als we in dit geval WHERE in plaats van HAVING hadden gebruikt, hadden we een melding gekregen dat er een fout in de syntax op de plaats van de WHERE zat.
Een ander verschil tussen WHERE en HAVING is het moment waarop er op de conditie getest wordt. Bij een WHERE gebeurt dit voor de individuele records vóór het groeperen, bij een HAVING gebeurt dit voor groepstotalen na het groeperen.
Ook op dit punt wijkt MySQL weer af van de SQL standaard. Volgens de standaard mogen in een HAVING alleen kolommen opgenomen worden die in een GROUP BY clausule voorkomen of die subject zijn van een verzamelingsfunctie. MySQL echter staat toe dat in HAVING kolommen voorkomen die genoemd zijn in een SELECT, GROUP BY, externe subquery of in een verzamelingsfunctie. Wederom zou ik hier ook willen aanraden om alleen de SQL standaard te gebruiken.
Wederom eerst de basisgegevens waar we mee werken:
+----+-------+--------+------------+
| 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 |
+----+-------+--------+------------+
Voorbeeld 12: Deelnemers met een totaal aantal punten > 200
SELECT naam, SUM(aantal) AS totaal
FROM punten
GROUP BY naam
HAVING SUM(aantal) > 200
Dit geeft de volgende resultaten:
+-------+--------+
| naam | totaal |
+-------+--------+
| Karel | 380 |
| Piet | 250 |
+-------+--------+
In dit geval zijn we gebonden aan het gebruik van HAVING omdat we vergelijking met een verzamelingsfunctie als criterium gebruiken. Als we in dit geval WHERE in plaats van HAVING hadden gebruikt, hadden we een melding gekregen dat er een fout in de syntax op de plaats van de WHERE zat.
Een ander verschil tussen WHERE en HAVING is het moment waarop er op de conditie getest wordt. Bij een WHERE gebeurt dit voor de individuele records vóór het groeperen, bij een HAVING gebeurt dit voor groepstotalen na het groeperen.
Ook op dit punt wijkt MySQL weer af van de SQL standaard. Volgens de standaard mogen in een HAVING alleen kolommen opgenomen worden die in een GROUP BY clausule voorkomen of die subject zijn van een verzamelingsfunctie. MySQL echter staat toe dat in HAVING kolommen voorkomen die genoemd zijn in een SELECT, GROUP BY, externe subquery of in een verzamelingsfunctie. Wederom zou ik hier ook willen aanraden om alleen de SQL standaard te gebruiken.
Pagina 7
GROUP BY Modifiers
Dit gedeelte van de tutorial is geen SQL standaard en derhalve alleen beschikbaar in MySQL. In MySQL is er een modifier die je mee kunt geven aan een GROUP BY query: WITH ROLLUP. De werking van deze modifier is een beetje tricky en hoewel ik niet zou aanraden om hem te gebruiken, zal ik voor de volledigheid de werking ervan wel uitleggen.
Als je deze modifier meegeeft aan een query met GROUP BY worden er extra records aan de resultaat set toegevoegd. Deze records bevatten het verder doorgerekende resultaat van een verzamelingsfunctie.
Ook hier nog even de basisgegevens:
Voorbeeld 13: Aantal punten per dag en totaal aantal punten
Dit resulteert in het volgende:
In deze resultaatset zien we in de eerste twee records het totaal aantal punten voor respectievelijk 11-7 en 12-7. Het derde record is het resultaat van de ROLLUP modifier en toont het totaal aantal punten, oftewel de som van het aantal punten uit de voorgaande records.
Laten we eens bekijken van deze functie doet als we groeperen op naam.
Voorbeeld 14: Aantal punten per dag en totaal
Het volgende resultaat komt naar voren:
We zien nu dus het totaal aantal punten per speler en in het laatste record, het record gecreerd door de ROLLUP modifier, zien we het totaal van het totaal aantal punten per speler. Oftewel, het totaal aantal behaalde punten in de wedstrijd.
Voorbeeld 15: Groeperen op meerdere kolommen met ROLLUP
Het resultaat van deze query is als volgt:
In deze resultaatset zien we per speler eerst het aantal punten per dag en vervolgens worden die waarden door de ROLLUP modifier bij elkaar opgeteld tot het totaal aantal punten per speler. Vervolgens zorgt de ROLLUP modifier ervoor dat in het laatste record de totaal aantallen punten per speler bij elkaar opgeteld worden om zo het totaal aantal behaalde punten weer te geven.
Nu heb ik in al deze voorbeelden de SUM() functie gebruikt, maar natuurlijk zijn alle verzamelingsfuncties hierbij te gebruiken.
Voorbeeld 16: AVG() en ROLLUP
Het resultaat:
Nu zien we dat de records gecreëerd door de ROLLUP modifier de gemiddelde van de voorgaande records bevatten. De ROLLUP modifier zorgt er dus voor dat dezelfde verzamelingsfunctie toegepast wordt op de voorgaande resultaten. Uiteindelijk krijg je er dus een recursief resultaat uit.
Nu zul je denken dat dit een hele fantastische functie is, integendeel. Zoals je wel kunt zien zijn de resultaten van deze query heel lastig te interpreteren. Het is namelijk niet direct duidelijk in welk record de ROLLUP gegevens zich bevinden en daarom is dit eigenlijk ook alleen te gebruiken als je precies weet hoeveel records van elke groep je in je tabel hebt staan.
Het gevaar is dus dat als je een nieuw aantal punten toevoegt aan de database, je heel je php script moet wijzigen omdat het aantal records veranderd is. Dat is ook de reden waarom ik het gebruik van deze functie afraadt.
Als je deze modifier meegeeft aan een query met GROUP BY worden er extra records aan de resultaat set toegevoegd. Deze records bevatten het verder doorgerekende resultaat van een verzamelingsfunctie.
Ook hier nog even de basisgegevens:
+----+-------+--------+------------+
| 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 |
+----+-------+--------+------------+
Voorbeeld 13: Aantal punten per dag en totaal aantal punten
SELECT datum, SUM(aantal) AS totaal
FROM punten
GROUP BY datum WITH ROLLUP
Dit resulteert in het volgende:
+------------+--------+
| datum | totaal |
+------------+--------+
| 2007-07-11 | 525 |
| 2007-07-12 | 460 |
| NULL | 985 |
+------------+--------+
In deze resultaatset zien we in de eerste twee records het totaal aantal punten voor respectievelijk 11-7 en 12-7. Het derde record is het resultaat van de ROLLUP modifier en toont het totaal aantal punten, oftewel de som van het aantal punten uit de voorgaande records.
Laten we eens bekijken van deze functie doet als we groeperen op naam.
Voorbeeld 14: Aantal punten per dag en totaal
SELECT naam, SUM(aantal) AS totaal
FROM punten
GROUP BY naam WITH ROLLUP
Het volgende resultaat komt naar voren:
+-------+--------+
| naam | totaal |
+-------+--------+
| Karel | 380 |
| Kees | 165 |
| Piet | 250 |
| Tom | 190 |
| NULL | 985 |
+-------+--------+
We zien nu dus het totaal aantal punten per speler en in het laatste record, het record gecreerd door de ROLLUP modifier, zien we het totaal van het totaal aantal punten per speler. Oftewel, het totaal aantal behaalde punten in de wedstrijd.
Voorbeeld 15: Groeperen op meerdere kolommen met ROLLUP
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum WITH ROLLUP
Het resultaat van deze query is als volgt:
+-------+------------+--------+
| naam | datum | totaal |
+-------+------------+--------+
| Karel | 2007-07-11 | 230 |
| Karel | 2007-07-12 | 150 |
| Karel | NULL | 380 |
| Kees | 2007-07-11 | 135 |
| Kees | 2007-07-12 | 30 |
| Kees | NULL | 165 |
| Piet | 2007-07-11 | 160 |
| Piet | 2007-07-12 | 90 |
| Piet | NULL | 250 |
| Tom | 2007-07-12 | 190 |
| Tom | NULL | 190 |
| NULL | NULL | 985 |
+-------+------------+--------+
In deze resultaatset zien we per speler eerst het aantal punten per dag en vervolgens worden die waarden door de ROLLUP modifier bij elkaar opgeteld tot het totaal aantal punten per speler. Vervolgens zorgt de ROLLUP modifier ervoor dat in het laatste record de totaal aantallen punten per speler bij elkaar opgeteld worden om zo het totaal aantal behaalde punten weer te geven.
Nu heb ik in al deze voorbeelden de SUM() functie gebruikt, maar natuurlijk zijn alle verzamelingsfuncties hierbij te gebruiken.
Voorbeeld 16: AVG() en ROLLUP
SELECT naam, datum, AVG(aantal) AS gemiddelde
FROM punten
GROUP BY naam, datum WITH ROLLUP
Het resultaat:
+-------+------------+------------+
| naam | datum | gemiddelde |
+-------+------------+------------+
| Karel | 2007-07-11 | 230.0 |
| Karel | 2007-07-12 | 150.0 |
| Karel | NULL | 190.0 |
| Kees | 2007-07-11 | 135.0 |
| Kees | 2007-07-12 | 30.0 |
| Kees | NULL | 82.5 |
| Piet | 2007-07-11 | 80.0 |
| Piet | 2007-07-12 | 90.0 |
| Piet | NULL | 83.3 |
| Tom | 2007-07-12 | 190.0 |
| Tom | NULL | 190.0 |
| NULL | NULL | 123.125 |
+-------+------------+------------+
Nu zien we dat de records gecreëerd door de ROLLUP modifier de gemiddelde van de voorgaande records bevatten. De ROLLUP modifier zorgt er dus voor dat dezelfde verzamelingsfunctie toegepast wordt op de voorgaande resultaten. Uiteindelijk krijg je er dus een recursief resultaat uit.
Nu zul je denken dat dit een hele fantastische functie is, integendeel. Zoals je wel kunt zien zijn de resultaten van deze query heel lastig te interpreteren. Het is namelijk niet direct duidelijk in welk record de ROLLUP gegevens zich bevinden en daarom is dit eigenlijk ook alleen te gebruiken als je precies weet hoeveel records van elke groep je in je tabel hebt staan.
Het gevaar is dus dat als je een nieuw aantal punten toevoegt aan de database, je heel je php script moet wijzigen omdat het aantal records veranderd is. Dat is ook de reden waarom ik het gebruik van deze functie afraadt.
Pagina 8
Slotwoord en referenties
Tot zover deze tutorial over het correcte gebruik van GROUP BY in SQL queries. Ik hoop dat het een ieder na het lezen van deze tutorial duidelijk is hoe een GROUP BY clausule gebruikt hoort te worden. Het is namelijk zonde als er door misbruik van deze functie onvolledige of incorrecte informatie op je website getoond wordt.
Opmerkingen, vragen en gevonden fouten zijn natuurlijk altijd welkom.
Deze tutorial is ook hier te vinden.
Enkele bronnen en referenties:
- MySQL 5.0 Reference Manual
Opmerkingen, vragen en gevonden fouten zijn natuurlijk altijd welkom.
Deze tutorial is ook hier te vinden.
Enkele bronnen en referenties:
- MySQL 5.0 Reference Manual
Reacties
0