GROUP BY: Het juiste gebruik ervan

  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
  9. Reacties op deze tutorial

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:
Code
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 |
+----+-------+--------+------------+


Voorbeeld 13: Aantal punten per dag en totaal aantal punten
Code
1
2
3
SELECT datum, SUM(aantal) AS totaal
FROM punten
GROUP BY datum WITH ROLLUP

Dit resulteert in het volgende:
Code
1
2
3
4
5
6
7
+------------+--------+
| 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
Code
1
2
3
SELECT naam, SUM(aantal) AS totaal
FROM punten
GROUP BY naam WITH ROLLUP

Het volgende resultaat komt naar voren:
Code
1
2
3
4
5
6
7
8
9
+-------+--------+
| 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
Code
1
2
3
SELECT naam, datum, SUM(aantal) AS totaal
FROM punten
GROUP BY naam, datum WITH ROLLUP

Het resultaat van deze query is als volgt:
Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------+------------+--------+
| 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
Code
1
2
3
SELECT naam, datum, AVG(aantal) AS gemiddelde
FROM punten
GROUP BY naam, datum WITH ROLLUP

Het resultaat:
Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------+------------+------------+
| 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 gecreeerd 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.

Vorige Volgende